触发器 - ToB企服应用市场:ToB评测及商务社交产业平台

-- 触发器
-- 准备工作：日志表 user_logs
CREATE TABLE user_logs(
id INT(11) NOT NULL auto_increment,
operation VARCHAR(20) NOT NULL COMMENT '操作类型，insert/update/delete',
operate_time datetime NOT NULL COMMENT '操作时间',
operate_empno INT(11) NOT NULL COMMENT '操作的empno',
operate_params VARCHAR(1000) COMMENT '操作参数',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据触发器(insert)
CREATE TRIGGER emp_insert_trigger
AFTER INSERT ON emp FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(null, 'insert', NOW(), new.empno, CONCAT('输入的内容：',new.empno,new.ename, new.job, new.mgr,new.hiredate,new.sal,new.comm,new.deptno));
END;
-- 查看
SHOW TRIGGERS;
-- 删除
DROP TRIGGER emp_insert_trigger;
INSERT INTO emp VALUES(7800, 'TIM', 'SALESMAN',7902, '1980-12-15 00:00:00', 900, 1000, 20);
SELECT * FROM user_logs;
-- 修改数据触发器(update)
CREATE TRIGGER emp_update_trigger
AFTER UPDATE ON emp FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(null, 'update', NOW(), new.empno, CONCAT('更新之前的数据：','empno=',old.empno,'ename=',old.ename,'job=', old.job, 'mgr=',old.mgr,'hiredate=',old.hiredate,'sal=',old.sal,'comm=',old.comm,'deptno=',old.deptno,
'| 更新之后的数据：','empno=',new.empno,'ename=',new.ename,'job=', new.job, 'mgr=',new.mgr,'hiredate=',new.hiredate,'sal=',new.sal,'comm=',new.comm,'deptno=',new.deptno));
END;
SHOW TRIGGERS;
UPDATE emp SET sal = 1900 WHERE empno = 7800;
UPDATE emp SET sal = 1900 WHERE empno < 7800; -- empno < 7800 的记录有几条，就执行几次触发器
SELECT * FROM user_logs;
-- 删除数据的触发器（delete）
CREATE TRIGGER emp_delete_trigger
AFTER DELETE ON emp FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(null, 'delete', NOW(), old.empno, CONCAT('删除之前的数据：','empno=',old.empno,'ename=',old.ename,'job=', old.job, 'mgr=',old.mgr,'hiredate=',old.hiredate,'sal=',old.sal,'comm=',old.comm,'deptno=',old.deptno));
END;
SHOW TRIGGERS;
DELETE FROM emp WHERE empno = 7800;
SELECT * FROM user_logs;

复制代码