mysql触发器编写_MySQL编写触发器

触发器语法示例&＃xff1a;

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt trigger_name&＃xff1a;触发器的名称 tirgger_time&＃xff1a;触发时机&＃xff0c;为BEFORE或者AFTER trigger_event&＃xff1a;触发事件&＃xff0c;为INSERT、DELETE或者UPDATE tb_name&＃xff1a;表示建立触发器的表明&＃xff0c;就是在哪张表上建立触发器 trigger_stmt&＃xff1a;触发器的程序体&＃xff0c;可以是一条SQL语句或者是用BEGIN和END包含的多条语句 所以可以说MySQL创建以下六种触发器&＃xff1a; BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE AFTER INSERT,AFTER DELETE,AFTER UPDATE

创建多个执行语句的触发器&＃xff1a;

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN    执行语句列表(用分号隔开) END

如表&＃xff1a;t_device_status_info

1 创建触发器

CREATE TRIGGER t_device_status_info_update AFTER UPDATE on t_device_status_info FOR EACH ROW BEGIN insert into t_device_status_info_tmp_modify (id, device_id, &＃96;name&＃96;, &＃96;value&＃96;, state, create_time, update_time) values (new.id, new.device_id, new.name, new.value, new.state, new.create_time, new.update_time); END;

补充插入或更新

replace into t_device_status_info_tmp_modify (id, device_id, &＃96;name&＃96;, &＃96;value&＃96;, state, create_time, update_time) values (new.id, new.device_id, new.name, new.value, new.state, new.create_time, new.update_time);

2 创建一个删除表操作的触发器

CREATE TRIGGER t_device_status_info_del BEFORE DELETE on t_device_status_info FOR EACH ROW BEGIN insert into t_device_status_info_tmp_del (id, device_id, name, value, state, create_time, update_time) values (old.id, old.device_id, old.name, old.value, old.state, old.create_time, old.update_time); END;

3 查看触发器

SELECT * FROM information_schema.&＃96;TRIGGERS&＃96; where trigger_name&＃61;&＃39;update_t_device_status_info&＃39;;

4 删除触发器

DROP TRIGGER update_t_device_status_info;

DROP TRIGGER delete_t_device_status_info;