多列更新时如何触发 [英] how to trigger when multiple columns are updated

问题描述

如果要更新任一列，我想在这里触发更新查询。但是我得到的错误表表正在更改，触发器/函数可能看不到它。

here I want to trigger the update query if any one of the columns are updated. But I am getting error table table is mutating, trigger/function may not see it.

create or replace
  TRIGGER TRIGGER1 
    AFTER  UPDATE OF HOST_ID,ENABLED_FLAG,ACTIVE_FLAG,AGENT_COUNTER,USER_WAIT_FLAG ON  MONITOR_AGENT_STATUS 
  FOR EACH ROW
    BEGIN
update monitor_agent_status set active_flag='Y', enabled_flag='Y', agent_counter=0, user_flag='N';
 END;

推荐答案

您应该在BEFORE行中设置这样的值触发器：

You should set the values like this in a BEFORE row trigger:

create or replace
  TRIGGER TRIGGER1 
    BEFORE UPDATE OF HOST_ID,ENABLED_FLAG,ACTIVE_FLAG,AGENT_COUNTER,USER_WAIT_FLAG ON  MONITOR_AGENT_STATUS 
  FOR EACH ROW
    BEGIN
      :NEW.active_flag:='Y';
      :NEW.enabled_flag:='Y';
      :NEW.agent_counter:=0;
      :NEW.user_flag:='N';
 END;

您的第二个触发器（来自下面的评论）：

Your second trigger (from comments below):

CREATE OR REPLACE TRIGGER TRIGGER11 
BEFORE UPDATE OF HOST_ID ON HOST_CURR_TIME 
FOR EACH ROW 
DECLARE 
   NewHost_Time varchar(10); 
BEGIN 
   select HOST_ID 
   into   NewHost_Time 
   from   HOST_CURR_TIME 
   where  HOST_ID='ATLMB100'; 

   :new.HOST_ID:= case :new.HOST_ID 
                     when 'CCNAFE02' then NewHost_Time 
                     when 'OFCBSERV' then NewHost_Time 
                     else :new.HOST_ID 
                  end; 
END; 

这篇关于多列更新时如何触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！