触发器的应用

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;

转载于:https://www.cnblogs.com/spotatoman/p/9770736.html