MySQL的触发器

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是，触发器无需调用，当数据库表中的执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
• 在MySQL，只有执行INSERT,DELETE,UPDATE操作时才能触发触发器的执行。
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
• 使用别名OLD和NEW来应用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器的特性：

1. 什么条件会触发：I、D、U
2. 什么时候触发：在增删改之前或之后
3. 触发频率：针对每一行执行
4. 触发器定义在表上，附着在表上

基本操作

创建触发器

1.创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
执行语句；
2.创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE||AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;

例如：

-- 数据准备
CREATE DATABASE mydb10_trigger;
USE mydb10_trigger;

-- 用户表
CREATE TABLE user(
uid INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);

-- 用户信息操作日志表
CREATE TABLE user_logs(
id INT PRIMARY KEY AUTO_INCREMENT,
time TIMESTAMP,
log_text VARCHAR(255)
);

-- 需求一：当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器：trigger_test1
CREATE TRIGGER trigger_test1 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),'有新用户添加');

-- 在user表添加数据，让触发器自动执行
INSERT INTO user VALUES(1,'张三','123456');
INSERT INTO user VALUES(2,'李四','234567'),(3,'王五','345678');

-- 需求二：当user表数据被修改，则会自动在user_log添加日志记录
DELIMITER $$
CREATE TRIGGER trigger_test2 BEFORE UPDATE
ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES (NULL,NOW(),'有用户信息被修改');
END $$
DELIMITER ;

-- 在user表修改数据，让触发器自动执行
UPDATE user SET password = '888888' WHERE uid = 1;
UPDATE user SET password = '666666' WHERE uid = 1;

NEW和OLD

概念：MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体的：

方法：NEW.columnName (columnName为相应数据表某一列名)

-- 数据准备
CREATE DATABASE mydb10_trigger;
USE mydb10_trigger;

-- 用户表
CREATE TABLE user(
uid INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);

-- 用户信息操作日志表
CREATE TABLE user_logs(
id INT PRIMARY KEY AUTO_INCREMENT,
time TIMESTAMP,
log_text VARCHAR(255)
);

-- 需求一：当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器：trigger_test1
CREATE TRIGGER trigger_test1 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),'有新用户添加');

-- 在user表添加数据，让触发器自动执行
INSERT INTO user VALUES(1,'张三','123456');
INSERT INTO user VALUES(2,'李四','234567'),(3,'王五','345678');

-- 需求二：当user表数据被修改，则会自动在user_log添加日志记录
DELIMITER $$
CREATE TRIGGER trigger_test2 BEFORE UPDATE
ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES (NULL,NOW(),'有用户信息被修改');
END $$
DELIMITER ;

-- 在user表修改数据，让触发器自动执行
UPDATE user SET password = '888888' WHERE uid = 1;
UPDATE user SET password = '666666' WHERE uid = 1;


-- INSERT类型的触发器
-- NEW
-- 定义触发器：trigger_test3
CREATE TRIGGER trigger_test3 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),CONCAT('有新用户添加,信息为：',NEW.uid,NEW.username,NEW.password));
INSERT INTO user VALUES(4,'赵六','234567');

-- UPDATE类型的触发器

-- OLD
CREATE TRIGGER trigger_test4 AFTER UPDATE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT('有用户信息修改，信息修改之前为：',OLD.uid,OLD.username,OLD.password));

UPDATE user SET password ='9999999' WHERE uid=4;

-- NEW
CREATE TRIGGER trigger_test5 AFTER UPDATE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT_WS(',','有用户信息修改，信息修改之后为：',NEW.uid,NEW.username,NEW.password));

UPDATE user SET password ='000000' WHERE uid=1;

-- DELETE类型的触发器

-- OLD
CREATE TRIGGER trigger_test6 AFTER DELETE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT_WS(',','有用户信息被删除，被删除用户信息为：',OLD.uid,OLD.username,OLD.password));

DELETE FROM user WHERE uid=4;

其他操作

查看触发器

方法：
SHOW triggers;

删除触发器

方法：
DROP TRIGGER [IF EXISTS] trigger_name;

例如：

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_test6;

注意事项

1. MySQL中触发器不能对本表进行insert,update,delete操作，以免递归循环触发。
2. 尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么insert的效率就非常低了。
3. 触发器是针对每一行的；对增删改非常频繁的表上切忌不要使用触发器，因为他会非常消耗资源。