MySQL MySQL在插入后触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18728539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger on after insert
提问by Mishal Dholakia
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.
我是 MySQL 的新手。我有两个表 total_loaner 和 available_loaner。我正在尝试为 total_loaner 中添加的每个新行创建一个触发器，我想将该新行添加到 available_loaner。
Here how my tables look like:
这是我的表的样子：
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;
回答by peterm
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');
Note:
笔记：
- 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
DELIMITERcommand andBEGIN...ENDblock
- 从 table name 中删除单引号
total_loaner，因为引号有效地使其成为字符串文字而不是正确的标识符。如果需要，您可以使用反勾号，但这是不必要的，因为它不是保留字，也不包含任何特殊字符。 - 因为它是一个单语句触发器，现在你不需要使用
DELIMITER命令和BEGIN...END阻止
Here is SQLFiddledemo
这是SQLFiddle演示
回答by Mike Christensen
You probably need to set your delimiter:
您可能需要设置分隔符：
DELIMITER $$
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$$
DELIMITER ;
Right now, it's confusing the semi-colon at the end of the INSERTstatement with the end of the CREATE TRIGGERstatement.
现在，它混淆了INSERT语句末尾的分号和语句末尾CREATE TRIGGER。
回答by Jaimin
This one worked for me, more simplified version..
这个对我有用，更简化的版本..
CREATE TRIGGER new_loaner_added
AFTER INSERT ON `DB1`.`table_name`
FOR EACH ROW
INSERT INTO `DB2`.`table_name` (messageID, conversationID, fromJID)
VALUES (NEW.messageID,NEW.conversationID, NEW.fromJID);
回答by Mihai
AFTER INSERT ON `total_loaner`
Use backticks.
使用反引号。
