MySQL 触发器的创建 & 小示例

触发器创建的语法

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;

若本文哪里有错误的，望指正，谢谢！！！！

参考资料：CREATE TRIGGER statement