本文共 1412 字,大约阅读时间需要 4 分钟。
触发器 自动在后台触发程序执行 创建, 管理 trigger 需要授权 grant trigger 当前 mysql 5.1.26 不支持一个表, 一个动作(i/u/d) 具有多个触发器 语法 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END; trigger_time: 触发时间(BEFORE或AFTER) trigger_event: 事件名(insert或update或delete) create table tr1 ( id int ); create table total ( id int ); insert into total values ( 0 ); create table tr2 ( id int ); create table tr_count ( id int ); insert into tr_count values ( 0 ); ex1 - 计数器 当执行插入, 自动在 total 增加 1 当删除, 自动 -1 delimiter // create trigger to_insert after insert on tr1 for each row begin declare num int; select id into num from total; set num=num+1; update total set id=num; end; // delimiter ; delimiter // create trigger to_delete after delete on tr1 for each row begin declare num int; select id into num from total; set num=num-1; update total set id=num; end; // delimiter ;
ex2 - 自动计算列总和 delimiter // create trigger tr_sum_i after insert on tr2 for each row begin declare num int; select sum(id) into num from tr2; update tr_count set id=num; end; // delimiter ; delimiter // create trigger tr_sum_u after update on tr2 for each row begin declare num int; select sum(id) into num from tr2; update tr_count set id=num; end; // delimiter ; delimiter // create trigger tr_sum_d after delete on tr2 for each row begin declare num int; select sum(id) into num from tr2; update tr_count set id=num; end; // delimiter ;
转载地址:http://pqnni.baihongyu.com/