MySQL学习笔记十一：触发器

一、触发器概念

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

二、触发器创建

2.1、创建语法

CREATE [DEFINER = { 'user' | CURRENT_USER }]　TRIGGER trigger_nametrigger_time trigger_eventON table_nameFOR 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 ROWBEGIN 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 ROWBEGIN 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=utf8DROP 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 ROWBEGIN 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;

 

MySQL学习笔记十一：触发器亚马逊站内推广 · 实战技巧20讲、 黑石集团、 支付结算、 eBay2018年春季更新即将生效，教你4步玩转eBay店铺设计！、 Listing被锁定无法更改标题内容是什么原因？怎么办？、 Review又有新玩法！亚马逊支持"测评"套路、 研究表明，近一半的消费者"黑五""网一"在亚马逊度过！、 亚马逊标题新规！2019亚马逊最新标题写法及标题书写规则！、