12.6 什么是触发器
12.6.1 什么是触发器
触发器(trigger)是一种对表执行某操作后会触发其他命令的机制。
当执行INSERT、UPDATE和DELETE等命令时,作为触发器提前设置好的操作也会被执行。例如,创建一个触发器,当某表的记录发生更新时,就以此为契机将更新的内容记录到另一个表中。
触发器常作为处理的记录或处理失败时的备份使用。
12.7 创建触发器
12.7.1 触发器被触发的时机
触发器被触发的时机包括以下两种
触发器被触发的时机
BEFORE | 在对表处理之前触发 |
AFTER | 在对表处理之后触发 |
列值
OLD.列名 | 对表进行处理之前的列值 |
NEW.列名 | 对表进行处理之后的列值 |
也就是说,执行INSERT、UPDATE和DELETE命令之前的列值可以通过“OLD.列名”获得,执行这些命令之后的列值可以通过“NEW.列名”获得。
但是,根据命令的不同,有的列值可以取出来,有的列值不能被取出来,如下表所示:
触发器执行前后,列值是否可以取出来
命令 | 执行前(OLD.列名) | 执行后(NEW.列名) |
---|---|---|
INSERT | × | √ |
DELETE | √ | × |
UPDATE | √ | √ |
12.7.2 创建触发器
命令格式:
CREATE TRIGGER 触发器名 BEFORE(或AFTER) DELETE(或其他命令)
ON 表名 FOR EACH ROW
BEGIN
使用更新前(OLD.列名)或者更新后(NEW.列名)的处理
END
在触发器主体的描述中,各个命令的末尾需要加上“;”。与创建存储过程相同,需要事先将分隔符改为“//”等。
练习:创建将表tb1中删除的记录插入到表tb1_from中的触发器
首先创建和tb1相同格式的表tb1_from:
mysql> CREATE TABLE tb1_from LIKE tb1;
Query OK, 0 rows affected (0.04 sec)
mysql> DESC tb1_from;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
输入&执行结果:
mysql> DELIMITER //
mysql> CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW
-> BEGIN
-> INSERT INTO tb1_from(empid,name,age) VALUES(OLD.empid,OLD.name,OLD.age);
-> END//
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
验证触发器效果
现在,表tb1中删除的记录应该能够插入到表tb1_from中了。
将tb1中所有记录删除:
mysql> DELETE FROM tb1;
Query OK, 5 rows affected (0.01 sec)
mysql> SELECT * FROM tb1;
Empty set (0.00 sec)
mysql> SELECT * FROM tb1_from;
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高桥 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西泽 | 35 |
+-------+------+------+
5 rows in set (0.00 sec)
可以看到,删除的记录插入到了tb1_from中。
下面将插入到表tb1_from中的数据恢复到原来的表tb1中:
mysql> INSERT INTO tb1 SELECT * FROM tb1_from;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tb1;
+-------+------+------+
| empid | name | age |
+-------+------+------+
| A101 | 佐藤 | 40 |
| A102 | 高桥 | 28 |
| A103 | 中川 | 20 |
| A104 | 渡边 | 23 |
| A105 | 西泽 | 35 |
+-------+------+------+
5 rows in set (0.00 sec)
12.8 确认和删除触发器
确认触发器:SHOW TRIGGERS;
删除触发器:DROP TRIGGER 触发器名;