文章目录

  • 1 触发器的概念
  • 2 触发器的创建
  • 2.1 语法
  • 2.2 举例
  • 3 查看、删除触发器
  • 3.1 查看
  • 3.2 删除触发器
  • 4 new与old
  • 5 优缺点
  • 5.1 优点
  • 5.2 缺点
  • 6 注意点


1 触发器的概念

  • 触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。
  • 所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
  • 当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

2 触发器的创建

2.1 语法

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

2.2 举例

CREATE DATABASE trigger_test;
USE trigger_test;

CREATE TABLE test_trigger ( 
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_note VARCHAR ( 30 ) 
 );

CREATE TABLE test_trigger_log ( 
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_log VARCHAR ( 30 ) 
 );

#举例1	
#创建触发器
#创建名称为before_insert_test_tri的触发器,向test_trigger数据表插入数据之前,向test_trigger_log数据表中插入before_insert的日志信息。
CREATE TRIGGER before_insert_test_tri BEFORE INSERT ON test_trigger FOR EACH ROW
BEGIN
		INSERT INTO test_trigger_log ( t_log )
	VALUES
	( 'before insert...' );
END;

#测试
INSERT INTO test_trigger ( t_note )
VALUES
	( 'test' );
	
	#查询
SELECT
	* 
FROM
	test_trigger;
SELECT
	* 
FROM
	test_trigger_log;
	

#举例2
#创建名称为after_insert_test_tri的触发器,向test_trigger数据表插入数据之后,向test_triggerIog数据表中插入after insert的日志信息。
CREATE TRIGGER after_insert_test_tri AFTER INSERT ON test_trigger FOR EACH ROW
BEGIN
		INSERT INTO test_trigger_log ( t_log )
	VALUES
	( 'after insert' );
END;

#测试
INSERT INTO test_trigger ( t_note )
VALUES
	( 'test2' );
	
	#查询
SELECT
	* 
FROM
	test_trigger;
SELECT
	* 
FROM
	test_trigger_log;
	
	
#举例3：
#定义触发器“salarycheck_trigger"，基于员工表“employees"的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为'HY000'的错误，从而使得添加失败。
CREATE TRIGGER salary_check_trigger BEFORE
	INSERT ON employees FOR EACH ROW
	BEGIN
		DECLARE
			manager_sal DOUBLE;#领导工资
#查询到要添加的数据的manager的薪资
		SELECT
			salary INTO manager_sal 
		FROM
			employees 
		WHERE
			employee_id = NEW.manager_id ;#new代表当前插入的数据
		IF
			NEW.salary > manager_sal THEN
				SIGNAL SQLSTATE 'HY000' 
				SET MESSAGE_TEXT = '薪资高于领导错误';
		END IF;
END;
#测试   1644 - 薪资高于领导错误
INSERT INTO employees( employee_id, last_name, email, hire_date, job_id, salary, manager_id )
VALUES
	( 300, 'Tom', 'tom@126.com', CURDATE(), 'AD_VP', 150000, 103 );

3 查看、删除触发器

3.1 查看

#方式一：查看所有触发器
SHOW TRIGGERS;
#方式二：查看指定触发器
SHOW CREATE TRIGGER salary_check_trigger;
#方式三：从系统库中查看
SELECT * FROM information_schema.`TRIGGERS`;

3.2 删除触发器

#删除
DROP TRIGGER before_insert_test_tri;

4 new与old

  • 当使用insert语句的时候，要插入的数据就是new。
  • 当使用delete语句的时候，要删除的那一条数据就是old。
  • 当使用update语句的时候，被修改的旧数据就是old，要修改为的新数据就是new。

5 优缺点

5.1 优点

  • 触发器可以确保数据的完整性。
  • 触发器可以帮助我们记录操作日志。
  • 触发器还可以用在操作数据前，对数据进行合法性检查。

5.2 缺点

  • 触发器最大的一个问题就是可读性差（A表插入数据时报B表插入数据错误）。
  • 相关数据的变更，可能会导致触发器出错。

6 注意点

注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。