MYSQL抛出异常+禁止删除触发器、同步插入触发器

DROP TRIGGER IF EXISTS m;
CREATE TRIGGER m AFTER DELETE ON test.m FOR EACH ROW
BEGIN
DECLARE	msg VARCHAR (255);
IF old.scnt = 2 THEN -- old为伪记录占位符,scnt为字段名
SET msg = "sc中有记录无法删除";
SIGNAL SQLSTATE 'HY000' SET mysql_errno = 22, message_text = msg;-- HY000为系统内部错误号，22为自定义的显示错误号，msg为错误文本
END IF;
END;

 

插入一表另一表同步插入

DROP TRIGGER IF EXISTS t;
CREATE TRIGGER t
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id) values(new.tab1_id);

END

 

更新记录自动添加更新时间（插入补充需要另外新建before insert 触发器）

DROP TRIGGER IF EXISTS zhixiao_la.xzq_acct_info_2018q2_u;
CREATE TRIGGER zhixiao_la.xzq_acct_info_2018q2_u
before update ON zhixiao_la.acct_info_2018q2
FOR EACH ROW
BEGIN
set new.update_time=CURRENT_TIMESTAMP;
END;