mysql 触发器

目录

1、创建触发器

1.1 创建只有一个执行语句的触发器

1.2 创建有多个执行语句的触发器

2、查看触发器

2.1 SHOW TRIGGERS语句查看触发器信息

2.2 在 triggers表中查看触发器信息

3、触发器的使用

4、删除触发器

---

触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和 DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL 从5.0.2版本开始支持触发器。

1、创建触发器

触发器是由INSERT、UPDATE和 DELETE等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。例如，当学生表中增加了一个学生的信息时，学生的总数就必须同时改变。可以在这里创建一个触发器，每次增加一个学生的记录,就执行一次计算学生总数的操作。这样就可以保证每次增加学生的记录后，学生总数是与记录数是一致的。触发器触发的执行语句可能只有一个，也可能有多个。

1.1 创建只有一个执行语句的触发器

在 MySQL中，创建只有一个执行语句的触发器的基本形式如下:

create trigger 触发器名 before | after 触发事件
        on 表名 for each row 执行语句

其中，触发器名参数指要创建的触发器的名字;BEFORE 和AFTER参数指定了触发器执行的时间，“BEFORE”指在触发事件之前执行触发语句，AFTER表示在触发事件之后执行触发语句;“触发事件”参数指触发的条件,其中包括INSERT、UPDATE和 DELETE;“表名”参数指触发事件操作的表的名称;FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器;“执行语句”参数指触发器被触发后执行的程序。

下面创建一个由INSERT触发的触发器dept_trig1。代码如下:

结果显示触发器dept_trig1已经创建成功。当向department 表中执行INSERT操作时,数据库系统都会在 INSERT语句执行之前向trigger_time表中插入当前时间。下面向department表中插入一条记录，然后查看trigger_time表中是否执行INSERT操作。代码执行如下:

执行结果显示，在向 department表中执行INSERT操作时，trigger_time 中插入了当前的系统时间。从这个例子可以看出，INSERT成功的触发了触发器。

1.2 创建有多个执行语句的触发器

MySQL中，触发器触发的执行语句可能有多个。创建有多个执行语句的触发器的基本形式如下:

create trigger 触发器名 before | after 触发事件
        on 表名 for each row
        begin 
             执行语句列表
        end

其中，BEGIN 与 END之间的“执行语句列表”参数表示需要执行的多个执行语句的内容。不同的执行语句之间用分号隔开。

！！！一般情况下，MySQL默认是以“;”作为结束执行语句。在创建触发器过程中需要用到“;”。为了解决这个问题，可以用DELIMITER语句。如“DELIMITER&&”，可以将结束符号变成“&&”。当触发器创建完成后，可以用命令“DELIMITER;”来将结束符号变成“;”。

下面创建一个由 DELETE触发多个执行语句的触发器dept_trig2。代码如下:

执行结果显示，触发器创建成功。当在 department表中执行DELETE操作后，trigger_time表中将插入两条记录。代码执行如下:

执行结果显示，在向department表中执行DELETE操作时，trigger_time中插入了两条记录。从这个例子可以看出，触发器可以同时执行多条执行语句。

！！！MySQL 中，一个表在相同触发时间的相同触发事件，只能创建一个触发器。例如，在 department表中，触发事件INSERT，触发时间为AFTER 的触发器只能有一个。但是，可以定义触发事件为BEFORE的触发器。如果该表中执行INSERT语句，那么这个触发器就会自动执行。

2、查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法等信息。查看触发器的方法包括SHOW TRIGGERS语句和查询information_schema数据库下的triggers表等。

2.1 SHOW TRIGGERS语句查看触发器信息

MySQL 中，可以执行SHOW TRIGGERS语句来查看触发器的基本信息。其基本形式如下:

show triggers;

下面执行SHOW TRIGGERS语句的结果如下:

结果显示了所有触发器的基本信息。因为数据库中暂时只有两个触发器，所以只显示了这两个触发器的基本信息。

SHOW TRIGGERS语句无法查询指定的触发器，该语句只能查询所有触发器的信息。如果数据库系统中的触发器很多，将显示很多信息。这样不方便找到所需要的触发器的信息。因此，在触发器很少时，可以选择SHOW TRIGGERS语句。

2.2 在 triggers表中查看触发器信息

在MySQL中,所有触发器的定义都存在information_schema数据库下的 triggers表中。查询triggers表，可以查看到数据库中所有触发器的详细信息。查询的语句如下:

select * from information_schema.triggers;

其中，“*”表示查询所有的列的信息;“information_schema. triggers”表示information_schema数据库下面的triggers表。

下面是用SELECT语句查询triggers表中的信息。代码执行如下:

结果显示了所有触发器的详细信息。

同时，该方法可以查询指定触发器的详细信息。其语句基本形式如下:

select * from information_schema.triggers where trigger_name = '触发器名';

其中，“触发器名”参数指要查看的触发器的名称，需要用单引号引起来。

下面是用SELECT 语句查询触发器dept_trig1 的信息。代码执行如下:

结果显示了触发器dept_trig1的详细信息。这种方式可以查询指定的触发器，使用起来更加方便、灵活。

所有触发器的信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句从triggers表中查询触发器的信息。如果数据库中的触发器比较多时，那么triggers表中记录会比较多。使用SELECT语句查询时，最好通过TRIGGER_NAME字段进行查询。

3、触发器的使用

在MySQL中，触发器执行的顺序是BEFORE触发器、表操作(INSERT、UPDATE 和DELETE）和 AFTER触发器。下面通过一个示例演示这三者的执行顺序。

下面在department表上创建 BEFORE INSERT和 AFTER INSERT这两个触发器。在向department表中插入数据时，观察这两个触发器的触发顺序。创建触发器的代码如下:

【一个小插曲】创建before insert 触发器一直报错

大概意思是一个表不能在相同触发事件创建相同触发事件的触发器，因为前面创建了一名为dept_trig1的触发器，它正是before insert 触发器

 所以，把触发器dept_trig1删除。

触发器都创建好以后，向department表中插入一条记录。代码执行如下:

查询结果显示, before_insert和 after_insert触发器被激活。先激活before_insert触发器，然后再激活after_insert触发器。

在激活触发器时，对触发器中的执行语句存在一些限制。例如，触发器中不能包含START TRANSACTION、COMMIT 或ROLLBACK等关键词，也不能包含CALL语句。

在触发器执行过程中，任何步骤出错都会阻止程序向下执行。但是对于普通表来说，已经更新过的记录是不能回滚的。更新后的数据将继续保持在表中。因此，设计触发器时要认真考虑。

4、删除触发器

删除触发器指删除数据库中已经存在的触发器。MySQL中使用DROP TRIGGER语句来删除触发器。其基本形式如下:

drop　trigger　触发器名;

其中，“触发器名”参数指要删除的触发器的名称。如果只指定触发器名称，数据库系统会在当前数据库下查找该触发器。如果找到，就执行删除。如果指定数据库，数据库系统就会到指定的数据库下去查找触发器。例如，job.worker_trig 表示job数据库下的触发器worker_trig。

！！！如果不再需要某个触发器时，一定要将这个触发器删除。如果没有将这个触发器删除，那么每次执行触发事件时，都会执行触发器中的执行语句。执行语句会对数据库中的数据进行某些操作，这会造成数据的变化。因此，一定要删除不需要的触发器。

下面是执行DROP TRIGGER语句来删除触发器 dept_trig１。代码执行如下:

结果显示删除成功。为确定触发器是否真的删除，可以用SELECT语句来查询‘dept_trig1'的信息。SELECT语句执行如下:

 执行结果显示，不存在该记录。这说明触发器dept_trigl已经删除成功。