MySQL学习笔记十一：触发器

一、触发器概念

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

二、触发器创建

2.1、创建语法

CREATE [DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

2.2、创建语法关键词解释

2.3、触发执行内容OLD与NEW

OLD：表示将要删除的旧行（类似于SQL Server中的DELETED表，只不过前者是行记录，后者是表）。

NEW：表示将要插入的新行（类似于SQL Server中的INSERTED表，只不过前者是行记录，后者是表）。

注：因为是单行记录，可以将它们理解为面向对象语言中的对象，直接通过`OLD.字段名`和`NEW.字段名`来使用。

事件与OLD、NEW的对应关系：

由上可见，更新一条记录，实际上是先将原记录删除（临时保存在OLD中），再插入一条新的记录（临时保存在NEW中）。

三、触发器示例

3.1、触发自身表

1）需求描述：假设有个员工表，如果新入职人员（INSERT）的年龄小于18岁时，自动将其更新为18岁。

2）测试表创建：

DROP TABLE IF EXISTS EMP1;
CREATE TABLE `emp1` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3）触发器创建：

#当年龄小于18岁时自动更新为18岁
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert BEFORE INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF (NEW.AGE<18) THEN
        SET NEW.AGE=18;
    END IF;
END$$
DELIMITER ;

4）数据插入：

INSERT INTO EMP1 VALUES (1,'HELLO',17);

5）结果查询：

SELECT * FROM EMP1;

6）注意事项：

需要注意的是，MySQL触发器不允许对自身表进行更新，但是允许更新NEW。

因此，上面的需求假如想通过AFTER INSERT来更改EMP1表，是行不通的。

下面进行错误的演示：

#错误的演示
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert AFTER INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF (NEW.AGE<18) THEN
        UPDATE EMP1 SET AGE=18 WHERE ID=NEW.ID;
    END IF;
END$$
DELIMITER ;

3.2、触发其它表

1）需求描述：假设有两个表结构一样的表EMP1和EMP2，如果EMP1有新记录插入时而EMP2还没有，则将新记录也插入到EMP2中。

2）测试表创建：

DROP TABLE IF EXISTS EMP1;
CREATE TABLE `emp1` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

DROP TABLE IF EXISTS EMP2;
CREATE TABLE `emp2` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3）触发器创建：

#EMP1插入新记录而EMP2没有时，将新记录也插入到EMP2中。
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert AFTER INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM EMP2 WHERE ID=NEW.ID) THEN
        INSERT INTO EMP2 (ID,`NAME`,AGE) VALUES (NEW.ID,NEW.NAME,NEW.AGE);
    END IF;
END$$
DELIMITER ;

4）数据插入：

INSERT INTO EMP1 VALUES (1,'HELLO',18);

5）结果查询：

SELECT * FROM EMP2;

四、触发器查看

#查看当前数据库所有触发器
SHOW TRIGGERS;
 
#查看指定数据库所有触发器
SHOW TRIGGERS FROM TEST;

#查看指定数据库指定表所有触发器
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_SCHEMA='TEST' AND EVENT_OBJECT_TABLE='EMP1';

五、触发器删除

#直接删除触发器
DROP TRIGGER triEmp1ForInsert;

#先检查再删除触发器
DROP TRIGGER IF EXISTS triEmp1ForInsert;