触发器 · phper2020面试指南

# 触发器发器的特性： 1. 有begin end体，begin end;之间的语句可以写的简单或者复杂 2. 什么条件会触发：I、D、U 3. 什么时候触发：在增删改前或者后 4. 触发频率：针对每一行执行 5. 触发器定义在表上，附着在表上。也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。 ## 创建触发器 ```mysql CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt ``` trigger_name : 触发器名称，用户自行指定 trigger_time：触发时机，取值BEFORE（之前）、AFTER（之后） trigger_event : 出发事件，INSERT、UPDATE、DELETE。（插入、更新、删除） tbl_name : 需要建立触发器的表名。 trigger_stmt : 触发程序体，可以是一条SQL语句或是BEGIN和END包含的索条语句由上面，可以知道MYSQL可以创建6种类型的触发器。（BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE）（AFTER INSERT、AFTER UODATE、AFTER DELETE）并且一张表上不能创建两个相同类型的触发器，因此一张表上面最多能创建6种类型的触发器。 ### 案例 ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN DECLARE s VARCHAR(20) DEFAULT 'hello'; SET s = 'world'; UPDATE `member` SET `name` = s WHERE id = OLD.id; END ``` 当在删除test表的数据时，会在对应的member对应的id中添加name字段值为world。如果使用下面这种sql语句，一值得到null。 ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN DECLARE s VARCHAR(20) DEFAULT 'hello'; SET s= (select name from test where id = old.id); SET s = concat('a','hello'); UPDATE `member` SET `name` = s WHERE id = OLD.id; END ``` 这是由于select中old是在数据被删除了，再去查找，就会一直找不到。参看下面案例： ```mysql CREATE TRIGGER test_tt AFTER DELETE ON `test` FOR EACH ROW BEGIN UPDATE `member` SET `name` = old.name WHERE id = OLD.id; END ``` 在删除test表中的一行数据时，能够把删除之前的name数据写入member表中去。可以见得： old表示的是之前的一整行完整的数据，并且是通过 . 的形式访问字段内容。 ## 查看触发器 ### 1、SHOW TRIGGERS语句查看触发器信息 ```mysql mysql> SHOW TRIGGERS\G; *************************** 1. row *************************** Trigger: add_task_second_update_task Event: INSERT Table: test_custom_task_second Statement: update test_custom_task set `status`= new.`status`,paied=new.paied,mtime=new.mtime where task_id = new.task_id Timing: AFTER Created: NULL sql_mode: Definer: shiwenyuan character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci *************************** 2. row *************************** Trigger: update_task_second_update_task Event: UPDATE Table: test_custom_task_second Statement: update test_custom_task set `status`= new.`status`,settlement_status = new.settlement_status,paied = new.paied ,mtime=new.mtime where task_id = new.task_id Timing: AFTER Created: NULL sql_mode: Definer: shiwenyuan character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 2 rows in set (0.00 sec) ERROR: No query specified ``` ### 2、在information_schema.triggers表中查看触发器信息 ```mysql mysql> SELECT * FROM information_schema.triggers\G; *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: add_task_second_update_task EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: test_custom_task_second ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update test_custom_task set `status`= new.`status`,paied=new.paied,mtime=new.mtime where task_id = new.task_id 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: NULL SQL_MODE: DEFINER: shiwenyuan CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci *************************** 2. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: update_task_second_update_task EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: test_custom_task_second ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update test_custom_task set `status`= new.`status`,settlement_status = new.settlement_status,paied = new.paied ,mtime=new.mtime where task_id = new.task_id 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: NULL SQL_MODE: DEFINER: shiwenyuan CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci 2 rows in set (0.01 sec) ``` 此操作支持 where条件查询单个 ## 删除触发器 ```mysql drop trigger trigger_name ```