MySQL的触发器和存储过程一样,都是嵌入到MySQL的一段程序,触发器是通过事件触发某个操作,这些事件包括INSERT,UPDATE,DELETE语句,当一个数据表上的这些语句执行时,如果定义了相应的触发器,触发器就会执行。触发器也是一种特殊的存储过程。
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
例子:创建一个触发器(来自官方文档)
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
注:
触发器也可以包含多个SQL语句,只要用begin...end
语句块替换上面的单条语句就行了。
show triggers;
drop trigger <triggername>;