数据库-触发器

定义

它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

触发器的作用

可在写入数据表前，强制检验或转换数据。
触发器发生错误时，异动的结果会被撤销。
部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
可依照特定的情况，替换异动的指令 (INSTEAD OF)。
触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息

触发器的优点

（1）触发程序的执行是自动的。当对触发程序相关表的数据做出相应的修改后立即执行。
（2）触发程序可以通过数据库中相关的表进行层叠修改另外的表。
（3）触发程序可以实施比FOREIGN KEY约束、CHECK约束更为复杂的检查和操作。

语法

创建触发器

DELIMITER |
CREATE TRIGGER `<库名>`.`触发器名称>`
< [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] >
ON [dbo]<tableName> //dbo代表该表的所有者
FOR EACH ROW
BEGIN
	触发器程序体;
END |

<触发器名称>			 		 最多64个字符，它和MySQL中其他对象的命名方式一样
{ BEFORE | AFTER } 		     触发器时机
{ INSERT | UPDATE | DELETE } 触发的事件
ON <表名称> 					 标识建立触发器的表名，即在哪张表上建立触发器
FOR EACH ROW                 触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次
<触发器程序体>                 要触发的SQL语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能

查看触发器

 通过系统表triggers查看
mysql> use information_schema
mysql> select * from triggers\G     查看所有触发器
mysql> select * from triggers  where trigger_name='触发器名称'\G    查看某个触发器

删除触发器

DROP TRIGGER 解发器名称

实例

例一

1、创建表
mysql> create table stu(
    -> id int primary key auto_increment,
    -> name char(20)
    -> );

mysql> create table stu_total (id int );

2、给表中插入数据
mysql> insert into stu(name) values('a');
mysql> insert into stu_total values(1);

3、创建事件为insert的触发器
mysql> \d $$
mysql> create trigger stu_insert after insert
    -> on stu
    -> for each row
    -> begin
    ->  update stu_total set id=id+1;
    -> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> \d ;

4、插入数据测试
mysql> insert into stu(name) values('b'),('c');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0

5、查看是否成功
mysql> select * from stu_total;
+------+
| id   |
+------+
|    3 |
+------+

6、创建事件为delete的触发器
mysql> create trigger stu_delete after delete 
    -> on stu
    -> for each row
    -> begin
    ->  update stu_total set id=id-1;
    -> end $$
mysql> \d ;

7、删除数据
mysql> delete from stu;

8、查看是否成功
mysql> select * from stu_total;
+------+
| id   |
+------+
|    0 |
+------+

9、查看触发器
mysql> use information_schema
mysql> select * from triggers\G     查看所有的触发器
mysql> select * from triggers  where trigger_name='stu_insert'\G   查看名为stu_insert的触发器
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: stu_insert
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: stu
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
 update stu_total set id=id+1;
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2021-01-22 03:00:53.90
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci

10、删除触发器

例二

1、创建表
mysql> create  table tab1(
    -> id int primary key auto_increment,
    -> name varchar(50),
    -> sex enum('m','f'),
    -> age int
    -> );

mysql> create table tab2(
    -> id int primary key auto_increment,
    -> name varchar(50),
    -> salary double(10,2)
    -> );


2、创建触发器
建立触发器作用：tab1表删除记录后，自动将tab2表中对应记录删除
（1）删除事件
mysql> delimiter $$
mysql> create trigger t1 after delete
    -> on tab1
    -> for each row
    -> begin
    ->  delete from tab2
    ->  where name=old.name;
    -> end$$
mysql> delimiter ;

（2）更新事件触发器
mysql> \d $$
mysql>  create trigger t2 after update
    ->  on tab1
    ->  for each row
    ->  begin
    -> update tab2
    -> set name=new.name
    -> where name=old.name;
    ->  end$$
mysql> \d ;

（3）插入事件触发器
mysql>  \d $$
mysql>  create trigger t3 after insert
    ->  on tab1
    ->  for each row
    ->  begin
    -> insert into tab2(name)
    -> values(new.name);
    ->  end$$
mysql> \d ;

3、测试
（1）插入测试
mysql> insert into tab1(name) values('tom');
mysql> select * from tab2;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | tom  |   NULL |
+----+------+--------+

（2）更新测试
mysql> update tab1
    -> set name='wang'
    -> where name='tom';
mysql> select * from tab2;
+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | wang |   NULL |
+----+------+--------+

（3）删除测试
mysql> delete from tab1;
mysql> select * from tab2;
Empty set (0.00 sec)