触发器语法示例：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt trigger_name：触发器的名称 tirgger_time：触发时机，为BEFORE或者AFTER trigger_event：触发事件，为INSERT、DELETE或者UPDATE tb_name：表示建立触发器的表明，就是在哪张表上建立触发器 trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句 所以可以说MySQL创建以下六种触发器： BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE AFTER INSERT,AFTER DELETE,AFTER UPDATE
创建多个执行语句的触发器：
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表(用分号隔开) END
如表：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, `name`, `value`, 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, `name`, `value`, 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.`TRIGGERS` where trigger_name='update_t_device_status_info';
4 删除触发器
DROP TRIGGER update_t_device_status_info;
DROP TRIGGER delete_t_device_status_info;