MySQL触发器

触发器

满足了某些条件，触发了另外一件事。

什么条件触发：insert, delete, update

什么时候触发：前、后

触发频率：针对每一行执行

触发器定义在表上

！！！！！！警告：触发器尽量少用，不建议用！！

* 忘记触发器

* 耗时0.001s => insert 100000 => 100s

product 创建了一个触发器INSERT AFTER -> 对time表insert

修改了time表表结构 id

product => INSERT INTO TABLE VALUES (1,2,3,4,5)

会导致插入失败，因为触发器的表结构更改

用处：

日志记录

日志表 => who time 做了什么...

代码 -> python /shell -> 操作 + 日志 -> 便于追踪和审计

创建触发器：

CREATE TRIGGER <触发器名>

trigger_time(BEFORE|AFTER) trigger_event(INSERT|DELETE|UPDATE)

ON table FOR EACH ROW

trigger_body(执行语句)

测试触发器

创建time表用于触发器测试

>DROP TABLE IF EXISTS time;

>CREATE TABLE time(

-> id INT PRIMARY KEY AUTO_INCREMENT,

-> t DATETIME);

创建触发器

>CREATE TRIGGER trig1 AFTER INSERT ON product FOR EACH ROW INSERT INTO time(t) VALUES(NOW());

插入数据

>INSERT INTO product VALUES (111, 'test', 111, 'c2');

查看触发器是否触发成功-->time表有没有插入数据

>SELECT * FROM time;

+----+---------------------+

| id | t |

+----+---------------------+

| 1 | 2025-01-14 11:25:02 |

+----+---------------------+

>INSERT INTO product VALUES (112, 'test', 111, 'c2'),(113,'test',111,'c1');

>SELECT * FROM time;

+----+---------------------+

| id | t |

+----+---------------------+

| 1 | 2025-01-14 11:25:02 |

| 2 | 2025-01-14 11:25:57 |

| 3 | 2025-01-14 11:25:57 |

+----+---------------------+

定义触发器执行多条SQL语句

定义语句的分隔符（默认为;）常用 || / $$

DELIMITER ||

CREATE TRIGGER trig2 BEFORE INSERT

ON product FOR EACH ROW

BEGIN

INSERT INTO time(t)VALUES(now());

INSERT INTO time(t) VALUES(now());

END||

DELIMITER ; 把语句的分隔符定义回来

测试触发器是否会执行多条SQL语句

（多了三条--》第一个触发器一条第二个触发器两条--》成功）

>INSERT INTO product VALUES (114, 'test', 111, 'c2');

>SELECT * FROM time;

+----+---------------------+

| id | t |

+----+---------------------+

| 1 | 2025-01-14 11:25:02 |

| 2 | 2025-01-14 11:25:57 |

| 3 | 2025-01-14 11:25:57 |

| 4 | 2025-01-14 11:35:24 |

| 5 | 2025-01-14 11:35:24 |

| 6 | 2025-01-14 11:35:24 |

+----+---------------------+

查看触发器

>SHOW TRIGGERS\G

>SHOW CREATE TRIGGER trig2\G

>SELECT * FROM information_schema.triggers\G

>SELECT * FROM information_schema.triggers WHERE trigger_name="trig2"\G

删除触发器

DROP TRIGGER trig1;

DROP TRIGGER trig2;

修改触发器：很多数据库版本不支持ALTER TRIGGER