触发器的应用
DROP TRIGGER IF EXISTS act_no_desc;
// 创建触发器插入
CREATE TRIGGER act_no_desc
AFTER INSERT ON activity20180914_log FOR EACH ROW
BEGIN
if new.desctription is NULL THEN
INSERT INTO act_error (table_name, error_id, desc_error, error_time)
VALUES ('topic.activity_20180914_log',new.id,'缺少desc',new.create_time);
END IF;
END;
DROP TRIGGER IF EXISTS act_no_desc2;
// 创建触发器更新
CREATE TRIGGER act_no_desc2
AFTER UPDATE ON activity20180914_log FOR EACH ROW
BEGIN
IF new.desctription is NULL THEN
INSERT INTO act_error (table_name, error_id, desc_error, error_time)
VALUES ('tapic.activity_20180914_log',new.id, '缺少desc2', new.create_time);
END IF;
END;
//创建触发器执行插入执行的表
DROP TABLE if EXISTS act_error;
CREATE TABLE act_error (
id int unsigned auto_increment comment 'id',
table_name VARCHAR(64) NOT NULL comment '表名',
error_id int unsigned NOT NULL comment '错误的id',
desc_error VARCHAR(64) NOT NULL default 'no desc' comment '简介',
error_time TIMESTAMP not NULL DEFAULT CURRENT_TIMESTAMP comment '时间',
PRIMARY KEY (id)
)engine = InnoDB default charset=utf8 comment 'error记录';
//插入数据
INSERT INTO activity20180914_log (userid,activity_id,desctription) VALUES
(999,101,'这是错误');
//更新数据
UPDATE activity20180914_log SET userid=9,desctription=NULL
WHERE id=8292;
