链接:http://aiezu.com/article/mysql_trigger_syntax.html
一、同表
– 创建触发器(添加行)
DELIMITER $
drop trigger if exists hr_social_salary_detail_insert$
create trigger hr_social_salary_detail_insert
before insert on hr_social_salary_detail
for each row
begin
set new.all_total = new.large_medical_amount_total+new.basic_medical_amount_total+new.social_security_amount_total;
end$
DELIMITER ;
– 创建触发器(更新行)
DELIMITER $
drop trigger if exists hr_social_salary_detail_update$
create trigger hr_social_salary_detail_update
before update on hr_social_salary_detail
for each row
begin
set new.all_total = new.large_medical_amount_total+new.basic_medical_amount_total+new.social_security_amount_total;
end$
DELIMITER ;
二、多表
– 触发器(不同表)
– 创建测试表和插入测试数据
drop table if exists tmp2;
drop table if exists tmp3;
create table tmp2 (id int, name varchar(128)) default charset=‘utf8’;
create table tmp3 (fid int, name varchar(128)) default charset=‘utf8’;
insert into tmp2 values(1, ‘爱E族’);
insert into tmp3 values(1, ‘爱E族’);
– 创建触发器
DELIMITER $
drop trigger if exists tmp2_update$
create trigger tmp2_update
after update on tmp2
for each row
begin
update tmp3 set name=new.name where fid=new.id;
end$
DELIMITER ;
update tmp2 set name=‘aiezu11’ WHERE id=2