触发器
自动在后台触发程序执行
创建, 管理 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 );
创建, 管理 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 ;
