数据库触发器

1、什么叫触发器：
当满⾜⼀定的条件以后，它会触发⼀个动作的执⾏，trigger
触发器是⼀种特殊类型的存储过程，它是在特定的表上⾃动执⾏的代码。当特定的事件发⽣时，MySQL 触发器会
被⾃动激活。这些事件可能包括 INSERT、UPDATE 或 DELETE 操作，以及对特定列的更改。
2、触发器的执⾏不是由程序调⽤，也不是由⼿⼯启动，⽽是由事件来触发、激活从⽽实现执⾏
3、触发器是由⼀定的事件来触发的，对表的增删改操作、不包括查询，查询是没有触发器的
4、⽣产中⼀般不通过MySQL中触发器来实现这功能，是通过Java程序、Python程序代码来实现触发器

# 创建触发器
CREATE：
 [DEFINER = { user | CURRENT_USER }]
 TRIGGER trigger_name
 trigger_time trigger_event
 ON tbl_name FOR EACH ROW
 trigger_body

说明:
1. DEFINER：指定触发器的创建者，可以是⽤户或当前⽤户。
2. TRIGGER trigger_name：定义触发器的名称。
3. trigger_time：指定触发器的触发时间，可以是BEFORE或AFTER。
4. trigger_event：指定触发器的触发事件，可以是INSERT、UPDATE或DELETE。
5. ON tbl_name：指定触发器所在的表名。
6. FOR EACH ROW：表示触发器对每⼀⾏记录都会触发。
7. trigger_body：触发器的执⾏语句体，可以是SQL语句或存储过程。


# Demo需求: 在向⽤户表中插⼊新记录时，⾃动将该操作记录到⽤户审计表中：
1. 创建⼀个⽤户表和⼀个⽤户审计表：
CREATE TABLE users (
 id INT(11) AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 email VARCHAR(50)
);

CREATE TABLE user_audit (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 action VARCHAR(50),
 timestamp TIMESTAMP
);

2. 创建⼀个触发器，以便在向⽤户表中插⼊新记录时，⾃动将该操作记录到⽤户审计表中：
DELIMITER //

CREATE TRIGGER insert_user_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
 INSERT INTO user_audit (user_id, action, timestamp)
 VALUES (NEW.id, 'INSERT', NOW());
END//

DELIMITER ;

具体解释如下：
- CREATE TRIGGER：创建触发器的关键字。
- user_trigger：触发器的名称。
- AFTER INSERT ON user：触发器的触发时间和触发事件，表示在向⽤户表中插⼊新记录之后触发该触发器。
- FOR EACH ROW：表示触发器对每⼀⾏记录都会触发。
- BEGIN 和 END：表示触发器的执⾏体开始和结束。
- INSERT INTO user_audit：将操作记录插⼊到⽤户审计表中。
- (user_id, action, date)：指定插⼊的列。
- VALUES (NEW.user_id, 'insert', NOW())：指定插⼊的值，其中NEW.user_id表示新插⼊的⽤户ID，'insert'表
示操作类型为插⼊，NOW()表示当前时间。

该触发器会在每次向⽤户表中插⼊新记录时，⾃动将该操作记录到⽤户审计表中

3. 向⽤户表中插⼊⼀条新记录，例如：
INSERT INTO users (id, name, email) VALUES (1, 'xu', 'xu@qq.com');

4. 触发器就会⾃动将该操作记录到⽤户审计表中。通过以下查询来验证这⼀点：
mysql> SELECT * FROM user_audit;
+----+---------+--------+---------------------+
| id | user_id | action | timestamp           |
+----+---------+--------+---------------------+
|  1 |       1 | INSERT | 2025-03-12 14:24:35 |
+----+---------+--------+---------------------+
1 row in set (0.00 sec)
显示⼀个包含刚才插⼊的记录的结果集，其中包含⽤户 ID、动作类型（在本例中为“INSERT”）和时间戳。

5、查看触发器
mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: insert_user_audit
               Event: INSERT
               Table: users
           Statement: BEGIN
 INSERT INTO user_audit (user_id, action, timestamp)
 VALUES (NEW.id, 'INSERT', NOW());
END
              Timing: AFTER
             Created: 2025-03-12 14:23:04.06
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

#查询系统表information_schema.triggers的⽅式指定查询条件，查看指定的触发器信息。
mysql> SELECT * FROM information_schema.triggers WHERE trigger_schema = 'test' AND
    -> trigger_name = 'insert_user_audit'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: insert_user_audit
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: users
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
 INSERT INTO user_audit (user_id, action, timestamp)
 VALUES (NEW.id, 'INSERT', NOW());
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2025-03-12 14:23:04.06
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

trigger_schema 库
trigger_name 触发器名称

6、删除触发器
#DROP TRIGGER trigger_name;
mysql> DROP TRIGGER insert_user_audit;
Query OK, 0 rows affected (0.00 sec)

#快速的清空表：truncate table table_name;
mysql> truncate table users;
Query OK, 0 rows affected (0.00 sec)

触发器示例

#创建触发器，在向学⽣表INSERT数据时，学⽣数增加，DELETE学⽣时，学⽣数减少
mysql> use test;
mysql> CREATE TABLE student_info ( stu_id INT ( 11 ) NOT NULL AUTO_INCREMENT, stu_name
    -> VARCHAR ( 255 ) DEFAULT NULL, PRIMARY KEY ( stu_id ) );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE student_count (student_count INT(11) DEFAULT 0);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO student_count VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student_info;
Empty set (0.00 sec)

#创建增加和减少学⽣数量的触发器
mysql> CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR
    -> EACH ROW UPDATE student_count SET student_count=student_count+1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR
    -> EACH ROW UPDATE student_count SET student_count=student_count-1;
Query OK, 0 rows affected (0.00 sec)

# 查看创建好的触发器
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: trigger_student_count_insert
               Event: INSERT
               Table: student_info
           Statement: UPDATE student_count SET student_count=student_count+1
              Timing: AFTER
             Created: 2025-03-12 14:33:42.94
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: trigger_student_count_delete
               Event: DELETE
               Table: student_info
           Statement: UPDATE student_count SET student_count=student_count-1
              Timing: AFTER
             Created: 2025-03-12 14:34:14.50
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

#查看建好的student_info表，⽬前是空的
mysql> select * from student_info;
Empty set (0.00 sec)

#往⾥⾯插⼊⼀条数据；
mysql> insert student_info values(1,'xu');
Query OK, 1 row affected (0.00 sec)

#查看student_info表⾥⾯已经有了⼀条信息
mysql> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|      1 | xu       |
+--------+----------+
1 row in set (0.00 sec)

#查看student_count⾥⾯的统计数，
mysql> select * from student_count;
+---------------+
| student_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)