CREATE TRIGGER `UpDateStateTrigger` AFTER INSERT ON `commonauditorlog`
FOR EACH ROW BEGIN
IF NEW.AuditorRes > 0 THEN UPDATE commonapplytable set State = State+1 where ApplyID = NEW.ApplyID and State != -1;
ELSE UPDATE commonapplytable set State = -1 where ApplyID = NEW.ApplyID;
END IF;
END;
怎么转成MSQL!!
------解决方案--------------------
这样?
- SQL code
CREATE TRIGGER UpDateStateTriggerON commonauditorlogAFTER INSERTBEGIN IF NEW.AuditorRes > 0 UPDATE commonapplytable set State = State+1 where ApplyID = NEW.ApplyID and State != -1ELSE UPDATE commonapplytable set State = -1 where ApplyID = NEW.ApplyIDENDgo
------解决方案--------------------
你这里面的 NEW 是什么呢?
------解决方案--------------------
- SQL code
--使用insertedCREATE TRIGGER UpDateStateTriggerON commonauditorlogAFTER INSERTBEGIN IF inserted.AuditorRes > 0 UPDATE commonapplytable set State = State+1 where ApplyID = inserted.ApplyID and State != -1ELSE UPDATE commonapplytable set State = -1 where ApplyID = inserted.ApplyIDENDgo
------解决方案--------------------
- SQL code
CREATE TRIGGER UpDateStateTriggerON commonauditorlogAFTER INSERTBEGIN IF exists(select 1 from inserted where AuditorRes > 0 )UPDATE commonapplytable set State = a.State+1 from commonapplytable a inner jion inserted b on a.ApplyID = b.ApplyID where a.State != -1ELSE UPDATE commonapplytable set State = -1 from commonapplytable a inner join inserted b on a.ApplyID = b.ApplyIDENDgo
------解决方案--------------------
SQL Server 触发器不是逐行激发的,其只能由 insert 语句激发。
- SQL code
create trigger upDateStateTrigger on commonauditorlogfor insertas update t set t.state=(case when l.auditorres>0 then t.state+1 else t.state=-1 end) from commonapplytable t inner join inserted l on t.applyid=l.applyid where t.state<>-1;go