2.1 创建触发器语法

创建触发器的语法结构是：

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

说明：

  • 表名：表示触发器监控的对象。
  • BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
  • INSERT|UPDATE|DELETE：表示触发的事件。
    • INSERT 表示插入记录时触发；
    • UPDATE 表示更新记录时触发；
    • DELETE 表示删除记录时触发。
  • 触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

2.2 代码举例

举例1：

1、创建数据表：

  1. CREATE TABLE test_trigger (
  2. id INT PRIMARY KEY AUTO_INCREMENT,
  3. t_note VARCHAR(30)
  4. );
  7. CREATE TABLE test_trigger_log (
  8. id INT PRIMARY KEY AUTO_INCREMENT,
  9. t_log VARCHAR(30)
  10. );

2、创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。

  1. DELIMITER //
  3. CREATE TRIGGER before_insert
  4. BEFORE INSERT ON test_trigger 
  5. FOR EACH ROW
  6. BEGIN
  7.     INSERT INTO test_trigger_log (t_log)
  8.     VALUES('before_insert');
  10. END //
  12. DELIMITER ;

3、向test_trigger数据表中插入数据

  1. INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

4、查看test_trigger_log数据表中的数据

  1. mysql> SELECT * FROM test_trigger_log;
  2. +----+---------------+
  3. | id | t_log         |
  4. +----+---------------+
  5. |  1 | before_insert |
  6. +----+---------------+
  7. 1 row in set (0.00 sec)

举例2：

1、创建名称为after_insert的触发器，向test_trigger数据表插入数据之后，向test_trigger_log数据表中插入after_insert的日志信息。

  1. DELIMITER //
  3. CREATE TRIGGER after_insert
  4. AFTER INSERT ON test_trigger
  5. FOR EACH ROW
  6. BEGIN
  7.     INSERT INTO test_trigger_log (t_log)
  8.     VALUES('after_insert');
  9. END //
  11. DELIMITER ;

2、向test_trigger数据表中插入数据。

  1. INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');

3、查看test_trigger_log数据表中的数据

  1. mysql> SELECT * FROM test_trigger_log;
  2. +----+---------------+
  3. | id | t_log         |
  4. +----+---------------+
  5. |  1 | before_insert |
  6. |  2 | before_insert |
  7. |  3 | after_insert  |
  8. +----+---------------+
  9. 3 rows in set (0.00 sec)

举例3：定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。

  1. DELIMITER //
  3. CREATE TRIGGER salary_check_trigger
  4. BEFORE INSERT ON employees FOR EACH ROW
  5. BEGIN
  6.     DECLARE mgrsalary DOUBLE;
  7.     SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
  9.     IF NEW.salary > mgrsalary THEN
  10.         SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
  11.     END IF;
  12. END //
  14. DELIMITER ;

上面触发器声明过程中的NEW关键字代表INSERT添加语句的新记录。