531
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 ;
最後更新：2017-04-02 22:16:22