MySQL触发器

棋大约 5 分钟约 1608 字

触发器就是由事件来触发某个操作，这些事件包括INSERT、UPDATE和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行对应的操作。满足触发器的触发条件时，数据库系统就会自动执行触发器定义的程序语句，可以令某些操作之间的一致性得到协调。

创建触发器

创建只有一条语句的触发器

创建只有一条语句的触发器的语句如下。

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

其中，BEFORE表示在触发器时间之前执行触发语句，AFTER表示在触发时间之后执行触发语句；触发事件表示数据库操作触发事件，包括INSERT、UPDATE和DELETE语句；表名表示指定事件触发操作表的名称；FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；执行语句代表触发器被触发后执行的语句。

如创建一条以插入命令INSERT语句触发的触发器auto_save_time，在这之前先创建一个time_log表格。创建触发器的语法如下。

create trigger auto_save_time before insert on employees for each row insert into time_log (savetime) values (now());

可见触发器auto_save_time创建成功。该触发器表示在employees表中执行insert语句后自动在auto_time表中保存插入时间。现在我们插入一个信息。

可见，插入数据后显示了保存时间为23:27:12。

创建具有多条语句的触发器

创建具有多条语句的触发器语法如下。

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW
BEGIN 执行语句列表 END

我们创建另一个自动保存时间的触发器auto_save_time2，在执行INSERT语句后向time_log表中插入数据并显示，并在time_log2表中存放此操作的具体操作（先创建此数据表）（先删除前面创建的触发器，否则会创建两条记录。删除触发器的语法在后面讲）。创建此触发器的语法如下。

delimiter //
create trigger auto_save_time2 before insert on employees for each row 
begin
insert into time_log (savetime) values (now());
insert into time_log2 (savetime,act) values (now(),'delete');
end //

这里不放出结果了。

查看触发器

SHOW TRIGGERS语句

使用SHOW TRIGGERS查看触发器的基本信息。语法如下。

SHOW TRIGGERS;

或者

SHOW TRIGGERS\G

查看TRIGGERS表中触发器信息

MySQL中所有的触发器的信息都存在TRIGGER表中，从此表查看所有触发器信息的语句如下。

SELECT * FROM information_schema.triggers;

其中，information_schema是MySQL中默认存在的库，而information_schema是数据库中用于记录触发器信息的数据表。同通过SELECT语句查看触发器信息，结果与上图相同。但是如果想查看特定的触发器的名称，可以通过WHERE子句应用TRIGGER_NAME字段作为查询条件，代码如下。

select * from information_schema.triggers where trigger_name = 'auto_save_time'\G

使用触发器

触发器的执行顺序

下面通过一个实例演示触发器的执行顺序。

create trigger before_in before insert on employees
    for each row insert into timeinfo (info) values ('before');

create trigger after_in after insert on employees
    for each row insert into timeinfo (info) values ('after');

在执行这两部分代码之后，向employees表中插入一条记录后查看数据表timeninfo的插入情况。

可以看到我们插入数据时先是before触发器先被激活，after触发器后被激活。

触发器中不能包含START TRANSCATION、COMMIT或ROLLBACK等关键字，也不能包含CALL语句。触发器执行非常严密，每一环都息息相关，任何错误都可能导致程序无法向下执行。已经更新的数据表是不能回滚的，故在设计过程中一定要注意触发器的逻辑严密性。

使用触发器维护冗余数据

在数据库中，冗余数据的一致性非常重要。为了避免数据不一致的问题的发生，尽量不要人工维护数据，建议是用编程自动维护。例如通过触发器实现。下面我们使用触发器实现当添加一条商品销售信息时自动修改库存信息表中的库存数量。步骤如下

创建库存表tb_stock，包含字段id（编号）、good_name（商品名称）、number（库存）。

create table tb_stock
(
id        int auto_increment,
good_name varchar(50) not null,
number    int         not null,
constraint tb_stock_pk
    primary key (id)
);

创建商品销售表tb_sell，包括字段id（编号）、good_name（商品名称）、good_type（商品类型）、number（购买数量）、price（价格）、amount（订单总价）。

create table tb_sell
(
 id        int auto_increment,
 good_name varchar(50)    not null,
 good_type int            not null,
 number    int            not null,
 price     decimal(10, 2) not null,
 amount    int            not null,
 constraint tb_sell_pk
     primary key (id)
);

向库存信息表tb_stock中添加一条商品库存信息，代码如下。
```
insert into tb_stock (good_name,number) values ('马克杯 350ML',100);
```
运行结果

为商品销售信息tb_sell创建一个触发器，名称为auto_number，实现向商品销售信息表tb_sell中添加数据时自动更新库存信息表tb_stock的商品销售数量，代码如下。

delimiter //
create trigger auto_number after insert
    on tb_sell for each row
    begin
        declare sellnum int;
        select number from tb_sell where id = new.id into @sellnum;
        update tb_stock set number = number - @sellnum where good_name = '马克杯 350ML';
    end //

向商品销售记录表tb_sell中插入一条商品销售信息，代码如下。

insert into tb_sell (good_name,good_type,number,price,amount) values ('马克杯 350ML',1,1,29.80,29.80);

查看库存信息表tb_stock中商品“马克杯 350ML”的库存数量，代码如下。
```
select * from tb_stock where good_name = '马克杯 350ML';
```
运行结果

从上图可以看到，再插入一条销售记录后，库存变量变成了99，原来是100。

删除触发器

删除触发器的语法如下。

DROP TRIGGER 触发器名;

在使用完触发器后记得删除触发器，否则在执行某些操作时会造成数据的变化。

MySQL触发器

# 创建触发器

# 创建只有一条语句的触发器

# 创建具有多条语句的触发器

# 查看触发器

# SHOW TRIGGERS语句

# 查看TRIGGERS表中触发器信息

# 使用触发器

# 触发器的执行顺序

# 使用触发器维护冗余数据

# 删除触发器