mysql触发器

原创已于 2023-02-21 10:51:49 修改 · 341 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2023-01-04 14:27:12 首次发布

mysql 专栏收录该内容

7 篇文章

订阅专栏

需求描述：A表中的某个字段变更，在B表中更新对应的数据，如果B表中没有，就插入一条
CREATE trigger triggerStatus1
before update
on dorm
for each row
BEGIN
if new.businessFlowMode != old.businessFlowMode and (select count(1) from company_business_flow_mode_record where companyCode = old.companyCode) <= 0
THEN
INSERT INTO company_business_flow_mode_record (businessFlowModeRecordId, companyCode,oldBusinessFlowMode,newBusinessFlowMode,createTime,updateTime,isDelete)
VALUES (Func_GetDefaultInnerCode(),old.companyCode,old.businessFlowMode,new.businessFlowMode,now(),now(),0);
ELSE
UPDATE company_business_flow_mode_record SET oldBusinessFlowMode = old.businessFlowMode,newBusinessFlowMode = new.businessFlowMode,isDelete = 0,updateTime = now() where companyCode = old.companyCode;
END if;
END;

参数解释

triggerStatus1:触发器名
before:触发时机
update:触发事件
dorm:A表名
company_business_flow_mode_record:B表名