2023.6.13 06.数据库触发器

06.数据库触发器 1、什么叫触发器： 当满⾜⼀定的条件以后，它会触发⼀个动作的执⾏，trigger 触发器是⼀种特殊类型的存储过程，它是在特定的表上⾃动执⾏的代码。当特定的事件发⽣时，MySQL 触发器会 被⾃动激活。这些事件可能包括 INSERT、UPDATE 或 DELETE 操作，以及对特定列的更改。 2、触发器的执⾏不是由程序调⽤，也不是由⼿⼯启动，⽽是由事件来触发、激活从⽽实现执⾏ 3、触发器是由⼀定的事件来触发的，对表的增删改操作、不包括查询，查询是没有触发器的 4、⽣产中⼀般不通过MySQL中触发器来实现这功能，是通过Java程序、Python程序代码来实现触发器 5、创建触发器 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, 'Wing', '[email protected]');   4. 触发器就会⾃动将该操作记录到⽤户审计表中。通过以下查询来验证这⼀点： SELECT * FROM user_audit 显示⼀个包含刚才插⼊的记录的结果集，其中包含⽤户 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: 2023-05-25 11:18:37.63 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> USE information_schema; Database changed mysql> SELECT * FROM information_schema.triggers WHERE trigger_schema = 'wing' AND trigger_name = 'insert_user_audit'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: wing TRIGGER_NAME: insert_user_audit EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: wing 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: 2023-05-25 11:18:37.63 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 wing Database changed 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.01 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: 2023-05-25 11:46:08.77 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: 2023-05-25 11:46:37.16 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 rows in set (0.00 sec)   #查看建好的student_info表，⽬前是空的 mysql> select * from student_info; Empty set (0.00 sec) #往⾥⾯插⼊⼀条数据； mysql> insert student_info values(1,'wing'); Query OK, 1 row affected (0.00 sec) #查看student_info表⾥⾯已经有了⼀条信息 mysql> select * from student_info; +--------+----------+ | stu_id | stu_name | +--------+----------+ | 1 | wing | +--------+----------+ 1 row in set (0.00 sec) #查看student_count⾥⾯的统计数， mysql> select * from student_count; +---------------+ | student_count | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec)