插入后MySQL触发 [英] MySQL Trigger on after insert

问题描述

我是MySQL的新手.我有两个表total_loaner和available_loaner.我正在尝试为total_loaner中添加的每个新行创建一个触发器，我想将该新行添加到available_loaner中.

I am new to MySQL. I have two tables total_loaner and available_loaner. I am trying to create a trigger for every new row added in total_loaner, I would to add that new row to available_loaner.

这是我的桌子的样子:

CREATE TABLE `total_loaner` (
  `Kind` varchar(10) NOT NULL,
  `Type` varchar(10) NOT NULL,
  `Sno` varchar(10) NOT NULL,
  PRIMARY KEY (`Sno`)
)

CREATE TABLE `available_loaner` (
  `Kind` varchar(10) NOT NULL,
  `Type` varchar(10) NOT NULL,
  `Sno` varchar(10) NOT NULL,
  `Status` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`Sno`)
) 

我的触发器似乎不起作用.

My trigger does not seem to work.

CREATE TRIGGER new_loaner_added 
AFTER INSERT ON 'total_loaner' for each row
begin
INSERT INTO available_loaner (Kind, Type, Sno, Status)
Values (new.Kind, new.Type, new.Sno, 'Available');
END;

推荐答案

根据您的情况，您可以像这样重写触发器

In your case you can rewrite your trigger like this

CREATE TRIGGER new_loaner_added 
AFTER INSERT ON total_loaner
FOR EACH ROW 
  INSERT INTO available_loaner (Kind, Type, Sno, Status)
  VALUES (NEW.Kind, NEW.Type, NEW.Sno, 'Available');

注意:

• 从表名total_loaner中删除了单引号，因为引号实际上使它成为字符串文字，而不是适当的标识符.您可以根据需要使用反勾号，但这不是必需的，因为它不是保留字，并且不包含任何特殊字符.
• 因为它是单语句触发器，所以您现在不需要使用DELIMITER命令和BEGIN...END块

• single quotes removed from table name total_loaner, because quotes effectively makes it a string literal instead of a proper identifier. You can use back ticks if you want but it's unnecessary since it's not a reserved word and it don't contain any special characters.
• since it's a one-statement trigger now you don't need to use DELIMITER command and BEGIN...END block

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于插入后MySQL触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！