mysql中trigger的语法跟procedure和function类似。
1 创建
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
具体操作中也和function类似,例如:
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;
若只有一句trigger语句,则begin...end复合句式可以不用,如:
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
不同的地方是:
a trigger不能用call来调用
b trigger的语句中不能有事务的开启和结束,如 START TRANSACTION, COMMIT, or ROLLBACK等等。
2 查看trigger
SHOW TRIGGERS [{FROM | IN} db_name][LIKE 'pattern' | WHERE expr]
或者从information_schema.TRIGGERS中查看
SELECT * FROM INFORMATION_SCHEMA.TRIGGERSWHERE condition;
3 删除
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name