17、触发器

#===== ===== 17_触发器 ===== =====#
触发器是由事件来触发某个事件（增删改），当事件发生了自动触发器执行相应的操作

CREATE DATABASE test05_company;
USE test05_company;

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 AFTER
# 题1：创建before_insert_test_tri的触发器,向test_trigger数据表插入数据之前
# 向test_trigger_log数据表插入before_insert的日志信息。

DELIMITER //
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 //
DELIMITER ;

INSERT INTO test_trigger(t_note)
VALUES('Tom .. ');

SELECT * FROM test_trigger_log;


# 题2：创建after_insert_test_tri的触发器,向test_trigger数据表插入数据之后
# 向test_trigger_log数据表插入after_insert的日志信息。
DELIMITER //
CREATE TRIGGER after_insert_log
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN 
    INSERT INTO test_trigger_log(t_log)
    VALUES('after insert ... ');
END //
DELIMITER ;

INSERT INTO test_trigger(t_note)
VALUES('lucy .. ');

SELECT * FROM test_trigger_log;

CREATE TABLE employees
AS
SELECT *
FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT *
FROM atguigudb.`departments`;

# 题3：定义触发器salary_check_trigger，基于员工表'employees'的insert事件，在insert之前检查要添加的新员工薪资是否
# 大于领导工资，若大于则报sqlstate_value为‘HY000'的错误
DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DECLARE mgr_sal DOUBLE;
    
    SELECT salary FROM employees 
    WHERE employee_id = NEW.manager_id;
    
    IF NEW.salary > mgr_sal
        THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
    END IF;
END //
DELIMITER ;


/*2、查看触发器*/
# 方式一
SHOW TRIGGERS;

# 方式二
SHOW CREATE TRIGGER salary_check_trigger;

# 方式三
SELECT * FROM information_schema.`TRIGGERS`;

/*3、删除触发器*/
DROP TRIGGER trigger_name;


/*    ****      课后练习      ****    */
USE test05_company;

CREATE TABLE emps
AS
SELECT employee_id,last_name,salary
FROM atguigudb.`employees`;

#1. 复制一张emps表的空表emps_back，只有表结构，不包含任何数据
CREATE TABLE emps_back
AS
SELECT *
FROM emps
WHERE 1 = 2;

#2. 查询emps_back表中的数据
SELECT *
FROM emps_back;

#3. 创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录添加到emps_back表中
DELIMITER //
CREATE TRIGGER emps_insert_trigger
AFTER INSERT ON emps
FOR EACH ROW
BEGIN
    INSERT INTO emps_back(employee_id, last_name, salary)
    VALUES(NEW.employee_id, NEW.last_name, NEW.salary);
END //
DELIMITER ;

DROP TRIGGER emps_insert_trigger;

#4. 验证触发器是否起作用
DESC emps;
SELECT * FROM emps;

INSERT INTO emps
VALUES(300,'Tom',5600);

SELECT *
FROM emps_back;