mysql 第13章 触发器
2015-10-24
目录
DDL
CREATE TABLE employee ( name char(64) not null, email char(64), password char(64), PRIMARY key (name) ); CREATE TABLE log ( id int auto_increment, email char(64), status char(10), message text, ts timestamp, primary key (id) ); CREATE TRIGGER tr_employee_insert_after AFTER INSERT ON employee FOR EACH ROW INSERT INTO log(email,status,message) VALUES(NEW.email,'OK',CONCAT('Adding employee ',NEW.name)); CREATE TRIGGER tr_employee_delete_after AFTER DELETE ON employee FOR EACH ROW INSERT INTO log(email,status,message) VALUES(OLD.email,'OK',CONCAT('Removing employee ')); delimiter // CREATE TRIGGER tr_employee_update_after AFTER UPDATE ON employee FOR EACH ROW BEGIN IF OLD.name != NEW.name THEN INSERT INTO log(email,status,message) VALUES(OLD.email,'OK',CONCAT('Name change from ',OLD.name,'to',NEW.name)); END IF; IF OLD.password != NEW.password THEN INSERT INTO log(email,status,message) VALUES(OLD.email,'OK','Password change '); END IF; IF OLD.email != NEW.email THEN INSERT INTO log(email,status,message) VALUES(OLD.email,'OK',CONCAT('E-mail change to ',NEW.email)); END IF; END // delimiter ;
DML
SET @pass = PASSWORD('xyzzy'); SELECT SLEEP(2); INSERT INTO employee VALUES('mats','mats@example.com',@pass); SELECT SLEEP(2); UPDATE employee SET name = 'matz' WHERE email = 'mats@example.com'; SELECT SLEEP(2); SET @pass = PASSWORD('foobar'); SELECT SLEEP(2); UPDATE employee SET `password` = @pass WHERE email = 'mats@example.com'; SELECT SLEEP(2); DELETE FROM employee WHERE email = 'mats@example.com'; SELECT SLEEP(2); SELECT * FROM log;
参考资料
[1] 唐汉明.深入浅出MySQL 数据库开发、优化与管理维护(第2版)[M].北京:人民邮电出版社,2014
[2] Schwartz.高性能MySQL(第3版)[M].北京:电子工业出版社,2013
[3] 范德兰斯.MySQL开发者SQL权威指南 [M].北京:机械工业出版社,2008
[4] Forta.MySQL必知必会 [M].北京:人民邮电出版社,2009
[6] 第21章:触发程序
[7] mysql 触发器学习
[8] mysql之触发器trigger
[9] MySQL 触发器简单实例
[10] 数据库触发器详解
[11] MySql触发器以及实例
[12] Mysql高级之触发器
[13] mysql教程-触发器
[14] MySQL触发器使用详解
[15] mysql触发器学习
[16] mysql 触发器使用