数据库之触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

1.创建触发器

在MySQL中，创建触发器语法如下

 

CREATE TRIGGER trigger_name

trigger_time

trigger_event ONtbl_name

FOR EACH ROW

trigger_stmt

 

删除触发器：drop trigger +触发器名

其中：

trigger_name：标识触发器名称，用户自行指定；

trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

 

2.trigger_event 详解

 

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。

REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

INSERT 型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发；

UPDATE 型触发器：更改某一行时激活触发器，可能通过UPDATE 语句触发；

DELETE 型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE 语句触发。

 

3.BEGIN … END 详解

 

在MySQL中，BEGIN … END 语句的语法为：

BEGIN

[statement_list]

END

其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。

而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：

DELIMITER new_delemiter

new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：

DELIMITER $

在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

 

1.什么是触发器

2.创建、显示、删除

 

mysql> CREATETRIGGER t1

   -> AFTER                   //触发器time  :before和after

   -> INSERT ON data          //事件 :insert、update、delete

   -> FOR EACH ROW    

   -> BEGIN                   //触发器程序体

   -> DECLARE c int;          //声明一个int型变量c

   -> SET  c = (SELECT count FROMchars);

   -> UPDATE chars SET count = c+1;

   -> END

   -> //

Query OK, 0 rows affected (0.02 sec)

 

mysql> SHOW TRIGGERS \G;

*************************** 1. row***************************

            Trigger: t1

               Event: INSERT

               Table: data

          Statement: BEGIN

DECLARE c int;

SET c = (SELECT count FROM chars);

UPDATE chars SET count = c+1;

END

              Timing: AFTER

            Created: NULL

           sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

            Definer: root@localhost

character_set_client: gbk

collation_connection: gbk_chinese_ci

 Database Collation: utf8_general_ci

1 row in set (0.00 sec)

 

   -> //

ERROR 1065 (42000): Query was empty

mysql> DELIMITER ;

mysql> INSERT INTO data VALUES('张三');

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM data;

+------+

| name |

+------+

| 张三     |

+------+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM chars;

+-------+

| count |

+-------+

|    1 |

+-------+

1 row in set (0.00 sec)

 

mysql> INSERT INTO data VALUES('jack');

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM chars;

+-------+

| count |

+-------+

|    2 |

+-------+

1 row in set (0.00 sec)

 

mysql> DROP trigger t1;

 

 

 

 

 

mysql> SELECT * FROM data;

   -> //

+------+

| name |

+------+

| 小李    |

+------+

1 row in set (0.00 sec)

 

mysql> select * from chars;

   -> //

+-------+

| count |

+-------+

|    1 |

+-------+

1 row in set (0.00 sec)

//在添加data添加对象，chars count字段+1

mysql> create trigger t1

   -> after

   -> insert on data

   -> for each row

   -> begin

   -> declare c int;

   -> set c =(select count from chars);

   -> update chars set count = c+1;

   -> end

   -> //

Query OK, 0 rows affected (0.06 sec)

 

mysql> insert into data values ('小丽');

   -> //

Query OK, 1 row affected (0.04 sec)

 

mysql> select * from data;

   -> //

+------+

| name |

+------+

| 小李    |

| 小丽    |

+------+

2 rows in set (0.00 sec)

 

mysql> select * from chars;

   -> //

+-------+

| count |

+-------+

|    2 |

+-------+

1 row in set (0.00 sec)

//在添加data减少对象，chars count字段-1

删除这里是重新建的表和上面添加无关联

mysql> create trigger t4

   -> after

   -> delete on data

   -> for each row

   -> begin

   -> declare d int;

    -> set d = (select count from chars);

   -> update chars set count = d-1;

   -> end

   -> //

Query OK, 0 rows affected (0.03 sec)

 

mysql> delete from data where name = '小明';

   -> //

Query OK, 1 row affected (0.03 sec)

 

mysql> select * from data;

   -> //

+------+

| name |

+------+

| 小李    |

+------+

1 row in set (0.00 sec)

 

mysql> select * from chars;

   -> //

+-------+

| count |

+-------+

|    1 |

+-------+

1 row in set (0.00 sec)

 

练习:

向员工表employee插入一条员工记录,如果员工部门编号不存在，则先创建该部门.

 

mysql> select * from department;

   -> //

+------+--------+------------+

| d_no | d_name | d_location |

+------+--------+------------+

|  10 | 产品部      | 上海          |

|  30 | 人事部      | 广州           |

|  40 | 销售部      | 成都          |

+------+--------+------------+

3 rows in set (0.00 sec)

 

mysql> select * from employee;

   -> //

+------+--------+-------+---------+----------+----------+------------+

| e_no | e_name | e_sex | dept_no |e_job    | e_salary | hire_date  |

+------+--------+-------+---------+----------+----------+------------+

| 1002 | 王刚       | 1    |      30 | HR       |    1600 | 2014-10-9  |

| 1003 | 珊珊     | 0    |      30 | HR       |    1250 | 2014-11-7  |

| 1005 | 小丽      | 0    |      40 | 销售经理        |    2850 | 2015-2-10  |

| 1006 | 王二       | 1    |      10 | 产品策划       |    2450 | 2015-4-23  |

| 1007 | 小冬      | 1    |      40 | 销售经理        |    2750 | 2015-3-10  |

| 1008 | 码子       | 1    |      30 | HR       |    1600 | 2014-10-9  |

| 2001 | 小明      | 1    |      10 | 界面设计         |    2500 | 2016-02-10 |

+------+--------+-------+---------+----------+----------+------------+

7 rows in set (0.00 sec)

 

mysql> create trigger tname  //创建一个出发器

   -> before  //触发时间   (在插入员工employee表之前先判断创建部门)

   -> insert on employee  //在employee表中插入

   -> for each row

   -> begin   //开始

   -> declare b int;   //声明一个int型变量b

-> set b = (

//查询department表中d_no = @d.no的个数

-> selectcount(*) from department where d_no = @d.no);

   -> if b = 0 then

   -> insert into department values(  //向表中插入数据

   -> @d.no,@dname,@dlocation);

   -> end if;

   -> end

   -> //

Query OK, 0 rows affected (0.05 sec)

 

mysql> delimiter ;

 

mysql> show triggers \G;    //显示触发器结构

*************************** 1. row***************************

            Trigger: tname   //触发器名

               Event: INSERT   //出发事件为：添加

               Table: employee   表

          Statement: begin

declare b int;

set b = (

select count(*) from department where d_no= @d.no);

if b = 0 then

insert into department values(

@d.no,@dname,@dlocation);

end if;

end

              Timing: BEFORE   //时间

            Created: NULL   //null

           sql_mode:STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIO

            Definer: test@localhost

character_set_client: gbk

collation_connection: gbk_chinese_ci

 Database Collation: utf8_general_ci

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

mysql> set @d.no = 50;

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @dname = '软件部';

Query OK, 0 rows affected (0.00 sec)

 

mysql> set @dlocation = '成都';

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from department;

+------+--------+------------+

| d_no | d_name | d_location |

+------+--------+------------+

|  10 | 产品部      | 上海          |

|  30 | 人事部      | 广州           |

|  40 | 销售部      | 成都          |

+------+--------+------------+

3 rows in set (0.00 sec)

 

mysql> insert into employee values(

   -> 3001,'波波',1,50,'java开发',3500,'2016-12-31');

Query OK, 1 row affected (0.05 sec)

 

mysql> select * from department;

+------+--------+------------+

| d_no | d_name | d_location |

+------+--------+------------+

|  10 | 产品部      | 上海          |

|  30 | 人事部      | 广州           |

|  40 | 销售部      | 成都          |

|  50 | 软件部      | 成都          |

+------+--------+------------+

4 rows in set (0.00 sec)

 

mysql> select * from employee;

+------+--------+-------+---------+----------+----------+------------+

| e_no | e_name | e_sex | dept_no |e_job    | e_salary | hire_date  |

+------+--------+-------+---------+----------+----------+------------+

| 1002 | 王刚       | 1    |      30 | HR       |    1600 | 2014-10-9  |

| 1003 | 珊珊     | 0    |      30 | HR       |    1250 | 2014-11-7  |

| 1005 | 小丽      | 0    |      40 | 销售经理        |    2850 | 2015-2-10  |

| 1006 | 王二       | 1    |      10 | 产品策划       |    2450 | 2015-4-23  |

| 1007 | 小冬      | 1    |      40 | 销售经理        |    2750 | 2015-3-10  |

| 1008 | 码子       | 1    |      30 | HR       |    1600 | 2014-10-9  |

| 2001 | 小明      | 1    |      10 | 界面设计         |    2500 | 2016-02-10 |

| 3001 | 波波       | 1    |      50 | java开发         |    3500 | 2016-12-31 |

+------+--------+-------+---------+----------+----------+------------+

8 rows in set (0.00 sec)

 

mysql>