mysql触发器详解
语法
DELIMITER $
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt;
END$
DELIMITER;
描述
trigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。
案例
- 当insert表tb_b_student时,给tb_b_student_log日志表中添加数据;
- 业务:因为python开发人员无法正常操作mysql数据库进行insert,每次只能先delete表后,才能insert新的数据。
- 关键业务语法:
insert into tb_b_student_log select * from tb_b_student a left join tb_b_student b on a.id = b.id where b.id is null
DELIMITER $
CREATE TRIGGER tb_e_student_log_insert
AFTER INSERT ON tb_e_student
FOR EACH ROW
BEGIN
INSERT INTO tb_e_student_log
select * from tb_e_student a left join tb_e_student_log b on a.id = b.id where b.id is null;
END$
DELIMITER ;
查看触发器
1.语法查看
SHOW TRIGGERS;
2.表中查看
删除触发器
drop trigger 触发器名称;