插入和更新的 MySQL Fire Trigger
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1318224/
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 Fire Trigger for both Insert and Update
提问by Adam S-Price
Is it possible to fire a mysql trigger for both the insert and update events of a table?
是否可以为表的插入和更新事件触发 mysql 触发器？
I know I can do the following
我知道我可以做到以下几点
CREATE TRIGGER my_trigger
AFTER INSERT ON `table`
FOR EACH ROW
BEGIN
.....
END //
CREATE TRIGGER my_trigger
AFTER UPDATE ON `table`
FOR EACH ROW
BEGIN
.....
END //
But how can I do
但是我该怎么做
CREATE TRIGGER my_trigger
AFTER INSERT ON `table` AND
AFTER UPDATE ON `table`
FOR EACH ROW
BEGIN
.....
Is it possible, or do I have to use 2 triggers? The code is the same for both and I don't want to repeat it.
有可能吗，还是我必须使用 2 个触发器？两者的代码相同，我不想重复。
回答by derobert
You have to create two triggers, but you can move the common code into a procedure and have them both call the procedure.
您必须创建两个触发器，但您可以将公共代码移动到一个过程中并让它们都调用该过程。
回答by Al Zziwa
In response to @Zxaos request, since we can not have AND/OR operators for MySQL triggers, starting with your code, below is a complete example to achieve the same.
响应@Zxaos 的请求，由于我们不能为 MySQL 触发器设置 AND/OR 运算符，从您的代码开始，下面是一个完整的示例来实现相同的功能。
1. Define the INSERT trigger:
1. 定义 INSERT 触发器：
DELIMITER //
DROP TRIGGER IF EXISTS my_insert_trigger//
CREATE DEFINER=root@localhost TRIGGER my_insert_trigger
AFTER INSERT ON `table`
FOR EACH ROW
BEGIN
-- Call the common procedure ran if there is an INSERT or UPDATE on `table`
-- NEW.id is an example parameter passed to the procedure but is not required
-- if you do not need to pass anything to your procedure.
CALL procedure_to_run_processes_due_to_changes_on_table(NEW.id);
END//
DELIMITER ;
2. Define the UPDATE trigger
2.定义UPDATE触发器
DELIMITER //
DROP TRIGGER IF EXISTS my_update_trigger//
CREATE DEFINER=root@localhost TRIGGER my_update_trigger
AFTER UPDATE ON `table`
FOR EACH ROW
BEGIN
-- Call the common procedure ran if there is an INSERT or UPDATE on `table`
CALL procedure_to_run_processes_due_to_changes_on_table(NEW.id);
END//
DELIMITER ;
3. Define the common PROCEDURE used by both these triggers:
3. 定义这两个触发器使用的公共 PROCEDURE：
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_to_run_processes_due_to_changes_on_table//
CREATE DEFINER=root@localhost PROCEDURE procedure_to_run_processes_due_to_changes_on_table(IN table_row_id VARCHAR(255))
READS SQL DATA
BEGIN
-- Write your MySQL code to perform when a `table` row is inserted or updated here
END//
DELIMITER ;
You note that I take care to restore the delimiter when I am done with my business defining the triggers and procedure.
您注意到，当我完成定义触发器和过程的业务后，我会小心地恢复分隔符。
回答by Jeff_Alieffson
unfortunately we can't use in MySQL after INSERT or UPDATEdescription, like in Oracle
不幸的是，我们不能在 INSERT 或 UPDATE描述后在 MySQL 中使用，就像在 Oracle 中一样
