1.定义:
触发器和存储过程相似,都是嵌入到 MySQL 中的一段程序。触发器是由事件来触发某个操作。当数据库执行这些事件时,就会激活触发器来执行相应的操作。这些事件称为触发条件,在MySQL中,有UPDATE,INSERT,和DELETE。
- 在MySQL中,目前只有UPDATE,INSERT,和DELETE这三种操作,才会触发触发器,其他操作都不支持触发器。这和Oracle中的触发器有很大的区别,要注意区分。
- 在MySQL5.7.2版本之前,触发器的每个表每次事件,都只允许一个触发器。因此,每个表最多值允许有6个触发器。如果一个表有两个事件,INSERT和DELETE,那么就要分别创建一个触发器。从MySQL版本5.7.2+开始,可以为同一触发事件和操作时间定义多个触发器。
- 只有表才支持触发器,视图和临时表不支持。
2.语法:
CREATE TRIGGER TRIGGER_NAME
BEFORE/AFTER
UPDATE/INSERT/DELETE ON TABLE_NAME
FOR EACH ROW
#触发器主体
[BEGIN]
SQL操作
[END];
- 创建触发器:和创建存储过程是一样,用的都是CREATE 语句,但要注意,这里没有 OR REPLACE。
- TRIGGER_NAME:触发器名称。在MySQL中,要保证每个表中的触发器是唯一的,每一个数据库内的触发器可以不唯一,这就说明了一个数据库内的两个表可以有相同的触发器。这种行为在其他的数据库内是不被允许的。但是为了格式上的规范,还是要尽可能的保证TRIGGER_NAME的唯一性。
- BEFORE/AFTER:触发器被触发的时刻。你如果希望触发器在触发事件之前发生,就使用BEFORE,如果希望在触发事件之后发生,就使用AFTER。
- UPDATE/INSERT/DELETE:触发事件,触发条件。在MySQL中,只有这三种DDL操作支持触发器,其他操作都不被允许。
- FOR EACH ROW:受触发事件影响的每一行都要激活触发器的动作。这里指的是行级触发,在MySQL里,FOE EACH ROW不可省略,不支持语句级触发。
- 触发器主体:既可以是单独的一条SQL语句,也可以是由BEGIN…END组成的复杂结构块。
创建一个触发器T1,在对表customer做插入操作时,就会触发T1,之后会在日志表note中插入一条数据
CREATE TRIGGER T1
AFTER
INSERT ON customer
FOR EACH ROW # 触发器主体
INSERT INTO note(日期,目标,操作) VALUES(NOW(),'customer','insert');
3.删除触发器
和删除表和存储过程类似,都是使用DROP语句来删除触发器。
#删除触发器T1
DROP TRIGGER IF EXISTS T1;
- 触发器不能够更新和修改,想要修改一个触发器的内容,只能先将触发器删除,在创建一个新的触发器。
4.查询触发器
#在已知数据库内,查询触发器SHOW TRIGGERS;SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = '触发器名'
#查询所有的触发器
SELECT * FROM information_schema.TRIGGERS(WHERE TRIGGER_SCHEMA = '数据库名')
5.触发器类型OLD和NEW的使用
触发器在DDL操作之前或者之后触发,总会对目标表做出一定的改动,有时我们需要知道目标表改变前后的值,这就用到了OLD和NEW的用法。
顾名思义,OLD就是改变之前的值,是UPDATE之前或者是要被DELETE或者已经被DELETE的值。
而NEW,则是UPDATE之后的新值,即将INSERT或者已经INSERT的值。
具体实例:
#创建customer表
CREATE TABLE customer
(CUST_ID
int(11) NOT NULL AUTO_INCREMENT,
CUST_NAME
varchar(10) NOT NULL,
CUST_TEL
varchar(10) DEFAULT NULL,
PRIMARY KEY (CUST_ID
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#向表中插入数据
#创建一个update触发器
CREATE TRIGGER T2
AFTER
UPDATE ON customer
FOR EACH ROW
#old和new的使用方法:old.columnname/new.columnname(列名)
#将更新前后的值,赋值给两个变量
SELECT OLD.CUST_ID,NEW.CUST_ID INTO @OLD_ID,@NEW_ID;#触发触发器T2UPDATE customer SET CUST_ID = '10000' WHERE CUST_NAME = 'AAA';#查询OLD和NEWSELECT @OLD_ID,@NEW_ID;
6.触发器的限制
MySQL触发器覆盖标准SQL定义中的所有功能,但在应用程序使用中,它们也存在一些限制:
- 单一的触发器不可以与多个表或者多个事件相关联,如果想要对一个表进行多个触发操作,可以多创建几个触发器。
- MySQL触发器中不能使用SHOW,LOAD TABLE,LOAD DATA,BACKUP DATABASE, RESTOREFLUSH,RETURN语句。
- 不能使用隐式或显式提交或回滚的语句,例如COMMIT,ROLLBACK,START TRANSACTION, LOCK / UNLOCK TABLES,ALTER,CREATE,DROP,RENAME。
- 不能使用PREPARE和EXECUTE之类的准备语句。
- 不能出现动态SQL。
Original: https://www.cnblogs.com/zhouyang-123/p/16401126.html
Author: ISPEAKER
Title: MySQL中的触发器
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/621130/
转载文章受原作者版权保护。转载请注明原作者出处!