MYSQL进阶学习笔记七：MySQL触发器的创建，应用及管理！（视频序号：进阶_16,17)

知识点八：MySQL触发器的应用(16,17)

触发器的定义：

　　什么是触发器：

　　　　触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据是触发执行，他比数据库本身标准的功能有更精细和更复杂的数据控制能力。

　　它具有的特定：

　　　　监视地点：一般的表名

　　　　监视事件：UPDATE/DELETE/INSERT

　　　　触发时间：AFTER/BEFORE

　　　　触发时间：UPDATE/DELETE/INSERT

它不能直接被调用，是由数据库主动去执行！

 

触发器的创建：

　　触发器的创建语法：

CREATE　TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

　　注释：

　　　　触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

　　　　触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。

　　　　trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之间或之后触发。

　　　　trigger_event指明了激活触发程序的语句的类型。tigger_event可以是下述值之一：

　　　　　　INSERT:将新行插入表时激活触发程序，例如通过 INSERT,LOAD DATA和REPLACE语句。

--触发器插入测试
--在员工表中添加一个员工时，员工工资表也创建一个并给默认值
DELIMITER //
CREATE TRIGGER t_insertEmp AFTER INSERT
ON employees FOR EACH ROW
BEGIN
    INSERT salaries VALUES(NEW.emp_no,0,DEFAULT,DEFAULT);
END 
//

DELIMITER ;

INSERT INTO employees VALUES(6098,DEFAULT,'LI','JIA','M',DEFAULT);

　　　　　　UPDATE：更新某一行时激活触发程序，例如通过UPDATE语句；

--触发器更新测试
DELIMITER //
CREATE TRIGGER t_updateEmp AFTER UPDATE
ON employees FOR EACH ROW
BEGIN
    UPDATE salaries SET from_data= NEW.hire_data WHERE  emp_no = OLD.emp_no;
END 
//

DELIMITER ;

UPDATE employees SET hire_data='2015-4-3' WHERE emp_no=1001;

　　　　　　DELETE：从表中删除某一行时激活触发程序，例如通过DELETE和REPLACE语句。

--触发器删除测试
--员工信息表删除一列时，工资表也删除相应的列
DELIMITER //
CREATE TRIGGER t_deleteEmp BEFORE DELETE
ON employees FOR EACH ROW
BEGIN
    DELETE FROM salaries WHERE emp_no = OLD.emp_no;
END 
//

DELIMITER ;

DELETE FROM employees WHERE emp_no =6098;

 

　　请注意：

　　　　trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。

　　　　例如：关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。

　　　　　　可能会造成混淆的例子之一是INSERT INTO…ON DUPLICATE UPDATE…

　　　　　　　　语法：DEFORE INSERT 触发程序对于每一行将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于行上是否有重复键。

　　　　对于具有相同触发程序动作时间和时间的给定表，不能有两个触发器。

　　　　　　例如：对于某一表，不能有两个BEFORE UPDATE触发程序，但是可以有一个BEFORE UPDATE和一个BEFORE INSERT触发程序，或

　　　　　　　　一个BEFORE UPDATE触发程序和一个AFTER UPDATE触发程序。

　　　　trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN…END复合语句结构。这样，就能使用存储子程序中允许的相同语句。

特别说明：

1. 对于INSERT而言，新插入的行用NEW来表示，行中的每一列的值用NEW，列名来表示。
2. 对于DELETE而言，原本有一行，后来被删除，想引用被删除的这一行，用OLD来表示，OLD.列名可以引用被删除的行而言
3. 对于UPDATE而言，被修改的行，修改前的数据，用OLD来表示，OLD.列名引用被修改之前的值。修改后的数据，用NEW来表示，new.列名引用被修改之后行中的值。

 

触发器的管理：

　　查看所有触发器：

　　　　SHOW TRIGGERS;

　　MySQL中有一个information_schema.TRIGGERS表，存储所有库中的所有触发器，DESC information_schema.TRIGGERS;可以看到触发器的结构。

　　查看一个触发器：

　　　　SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = ‘触发器名字’\G

　　删除触发器：

　　　　DROP TRIGGER [schema_name.] trigger_name;