mysql 触发器

63 阅读 0 评论 42 点赞

我是靠谱客的博主呆萌西牛，最近开发中收集的这篇文章主要介绍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 ;