触发器操作

一.什么是触发器

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由某个事件来触发，比如当对一个表进行操作（insert,delete,update等）时就会激活其执行。

在有数据修改时自动强制执行其业务规则，经常用于加强数据的完整性约束和业务规则等。

二.触发器的语法

 CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

trigger_name是所创建的触发器的名称，与名称为tbl_name的表相关。这里tbl_name必须引用永久性表，并且不能将触发器与TEMPOPARY表或视图关联起来。

   trigger_time是触发程序的动作时间。它可以是 BEFORE 或 AFTER ，以指明触发程序是在激活它的语句之前或之后触发。

   trigger_event 指明了激活触程序的类型，以下：
1. insert： 将新行插入表时激活触发程序，例如通过insert、 load data 和 replace 语句

2. update： 更改某一行激活触发程序，例如通过update语句

3. delete： 从表中删除某一行激活触发程序，

   trigger_body 是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用 begin。。 end 复合语句结构。这样，就能使用存储子程序中允许的相同语句了。

例如：我们在mysql中创建一个名为 testref的触发器，相关表定义如下：

create table test1(a1 int);
create table test2(a2 int);
create table test3(a3 int not null auto_increment primary key);
create table test4(
a4 int not null auto_increment primary key,
b4 int default 0
);

触发器定义如下：

create trigger testref before insert on test1
 for each row begin
   insert into test2 set a2 = new.a1;
   delete from test3 where a3 = new.a1;
   update test4 set b4 = b4+1 where a4=new.a1;
end  

当向表test1 中插入数据时，表test2、test3、test4中的数据也会根据触发器规则随之改变。

三.触发器的使用

删除触发器：

drop trigger [schema_name] trigger_name

trigger_name是要删除的触发器的名称。方案名称(schema_name)可选。

下面来看一个例子。

在MySQL中创建一个表并为表创建一个触发器。SQL语句如下：

create table account(
acct_num int, 
amount decimal(10,2)
);
create trigger ins_sum before insert on account
for each row set @sum = @sum +new.amount;

首先创建了账户表account。 create trigger语句创建了与账户相关的名为ins_sum的触发程序。它还包括一些子句，这些子句指定了触发程序激活时间、触发程序事件，以及激活触发程序时做些什么。

  1. 关键词 before 指明了触发程序的动作时间。在本例中，应将每一行插入表之前激活触发程序。这类允许的其他关键词是 after

  2. 关键字 insert 指明了激活触发程序的时间。在本例中，insert语句将导致触发程序的激活。也可以为 delete 和update 语句创建触发程序。

  3. 跟在for each row 后面的语句定义了每次激活触发程序时将执行的程序，对于受触发语句影响的每一行执行一次。在本列中，触发的语句是简单的 SET 语句， 负责将插入  amount 列的值加起来。该语句将列引用为 new.amount，意思是  将要插入到新行的amount列的值

要使用触发程序，将累加器变量设置为0，执行insert语句，然后查看变量的值。

set @sum=0;
insert into account values (137,14.98), (141,1937.50),(97,-100.00);

select @sum as 'total amount inserted'

输出结果如下：

@sum的值是1852.48（即14.98+1937.50-100）

销毁触发使用：

drop trigger test.ins_sum