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;