MySql学习笔记，触发器的创建和使用

#触发器
#user表数据变更日志，增加、修改、删除
CREATE TABLE user_log (
	id INT ( 11 ) NOT NULL auto_increment,
	operation VARCHAR ( 20 ) NOT NULL COMMENT '操作类型：insert,update,delete',
	operation_time datetime NOT NULL COMMENT '操作时间',
	operation_id INT ( 11 ) NOT NULL COMMENT '操作表的ID',
	operation_params VARCHAR ( 500 ) COMMENT '操作参数',
PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT charset = utf8;

select * from user_log;

#根据三种不同的操作添加三个触发器
CREATE TRIGGER user_insert_trigger
AFTER INSERT
ON user
FOR EACH ROW
BEGIN
	INSERT INTO user_log(operation,operation_time,operation_id,operation_params)
	VALUES ('insert',now(),new.id,CONCAT(
	'插入后(id:',new.id,
	',age:',new.name,
	',age:',new.age,
	',stats:',new.stats,')'));
END;
#删除触发器
DROP TRIGGER user_update_trigger;
#插入数据
INSERT into user(name,age,stats) VALUES('dsa',35,0);

CREATE TRIGGER user_update_trigger
AFTER UPDATE
ON user
FOR EACH ROW
BEGIN
	INSERT INTO user_log(operation,operation_time,operation_id,operation_params)
	VALUES ('update',now(),new.id,CONCAT(
	'修改前(id:',new.id,
	',age:',old.name,
	',age:',old.age,
	',stats:',old.stats,'),修改后(id:',new.id,
	',age:',new.name,
	',age:',new.age,
	',stats:',new.stats,')'));
END;

UPDATE user set name = 'hahhhh' where id=23;

CREATE TRIGGER user_delete_trigger
AFTER DELETE
ON user
FOR EACH ROW
BEGIN
	INSERT INTO user_log(operation,operation_time,operation_id,operation_params)
	VALUES ('delete',now(),old.id,CONCAT(
	'删除前(id:',old.id,
	',age:',old.name,
	',age:',old.age,
	',stats:',old.stats,')'));
END;
#删除触发器
DROP TRIGGER user_delete_trigger;

DELETE FROM user where id=26;

select * from user_log;

show TRIGGERS;