触发器创建的语法
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
说明:
trigger_body是触发器激活时要执行的语句。
要执行多个语句,请使用 BEGIN ... END 复合语句构造。这也使您能够使用存储例程中允许的相同语句
trigger_event 是指激活触发器的操作类型
INSERT:每当在表中插入新行时,触发器就会激活。
UPDATE:只要修改一行,触发器就会激活 。
DELETE:只要从表中删除一行,触发器就会激活 。
注:
1、触发器不能只能建立在基本表上,不能建立在临时表和视图上;
2、对于给定的表,不能有多个具有相同触发事件和动作时间的触发。
例如,BEFORE UPDATE一个表不能有两个触发器。
但可以使用BEFORE UPDATE和BEFORE INSERT触发器,也可以使用BEFORE UPDATE 和AFTER UPDATE触发器。
示例1:累加某一列的值
-- 创建测试表
drop table if exists account;
create table account (acct_num int, amount decimal(10,2));
-- 创建触发器
-- 这个触发器最后的结果是累加 account 表中的 amount 值
drop trigger if exists insert_sum_test;
create trigger insert_sum_test
before insert on account
for each row
set @sum = @sum + new.amount;
-- 插入数据
set @sum = 0;
insert into account values(137,14.98);
insert into account values(141,1937.50);
insert into account values(97,-100.00);
-- 查看我们的结果
select @sum 'Total amount'; -- 1852.48 = 4.98 + 1937.50 - 100
结果:
示例2:当某一个的薪资 update 时,就插入详情一条记到表里:
-- 查看系统中所有的触发器:show triggers;
-- 创建测试表 dina_test_table
drop table if exists dina_test_table;
create table dina_test_table(
id int,
name char(255),
salary decimal(10,2));
-- 插入测试数据
insert into dina_test_table values(1,'Dina','8500.80');
insert into dina_test_table values(2,'Demon','8600.60');
insert into dina_test_table values(3,'Augenstern','8500');
insert into dina_test_table values(4,'Paradox','8500');
insert into dina_test_table values(5,'Yan','8000');
-- 看一下我们的测试数据
select * from dina_test_table;
-- 创建测试表 dina_test_table
-- 当 dina_test_table 表里有 update 后,会触发 update_salary_test 触发器,向此表插入数据
drop table if exists dina_test_log_table;
create table dina_test_log_table(
id int(11) not null AUTO_INCREMENT,
insert_time datetime ,
name char(255),
type char(10),
money decimal(10,2),
primary key (`id`));
-- 创建 update_salary_test 触发器
drop trigger if exists update_salary_test;
create trigger update_salary_test
after update on dina_test_table -- 在 dina_test_table 表有 update 后,此触发器会被激活
for each row
begin
-- 声明变量
declare type char(10);
declare money decimal(10,2);
declare name char(255);
declare new_salary decimal(10,2);
declare old_salary decimal(10,2);
-- 给变量赋值
set new_salary = new.salary , old_salary = old.salary , name = new.name;
if new_salary > old_salary then
set type ='加薪';
elseif new_salary < old_salary then
set type ='减薪';
else
set type ='薪资没变';
end if;
set money = abs(new_salary - old_salary);
-- 插入数据到 dina_test_log_table
insert into dina_test_log_table values(null,now(),name,type,money);
end;
说明:
使用 old(旧值)和new(新值)关键字,能够访问受触发程序影响的行中的字段值
在 insert 触发器中,仅能使用 new.column_name,没有旧值
在 delete 触发器中,仅能使用 old.column_name,没有新值
在 update 触发器中,能使用 old.column_name 来获取更新前的旧值,也能使用 new.column_name 来获取更新后行中的新值
update dina_test_table 表里的 salary 来激活触发器update_salary_test:
-- update dina_test_table 表里的 salary
update dina_test_table set salary='8000' where id='1';
update dina_test_table set salary='8700' where id='2';
update dina_test_table set salary='8600' where id='3';
update dina_test_table set salary='8000' where id='5';
-- 查看表 dina_test_log_table 的数据
select * from dina_test_log_table;
查看表 dina_test_log_table 的数据:
3、看我们刚才创建的两个触发器的相关信息
show triggers;
若本文哪里有错误的,望指正,谢谢!!!!