mysql 触发器

触发器

自动在后台触发程序执行

创建, 管理 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 ;