how to trigger when multiple columns are updated

Question

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;

Solution

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; 

Comments

[Shreenivas]

why we should take before row trigger ? why not after ?

[Tony Andrews]

You cannot modify the values in an AFTER trigger because the update has already been done.

[Shreenivas]

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'; (case HOST_ID when 'CCNAFE02' then :new.HOST_ID:=NewHost_Time when 'OFCBSERV' then :new.HOST_ID:=NewHost_Time else HOST_ID end); END;

[Shreenivas]

i made another trigger here I am fetching a data from one column and need to update to other two columns . can you check whether its correct or wrong and can we set a time also like after 1 minute it should trigger

[Tony Andrews]

I have amended that trigger and added to my answer. There were issues with your CASE statement, which I have turned into a CASE expression.

[Shreenivas]

may I know where is the answer ? I am not getting Can you paste code here ?

[Shreenivas]

Error(9,4): PL/SQL: Statement ignored Error(9,24): PLS-00201: identifier 'HOST_ID' must be declared .getting these two errors

[Tony Andrews]

I've guessed what you meant and fixed.

[Tony Andrews]

Try again with latest

[Sentinel]

Why select HOST_ID into a var where HOST_ID = some constant? You already know what HOST_ID will be assuming a record exists for that host id, and if the record doesn't exist the trigger error and the update will fail. Additionally if the hard coded HOST_ID does exist and there is a primary key or unique constraint on HOST_ID the update will fail if either of the when clauses in your case statement evaluates to true since :new.HOST_ID would violate the constraint. If there is no constraint then the select into will fail with too many rows once there are 2 or more rows with the same HOST_ID

[Shreenivas]

@Sentinel actually i need to add time over there . instead of time i wrongly put the host_id. actually we have 3 servers in that only one server is taking updated time and 2 more not taking time so that i am storing the host id's time in the variable and replacing it in other two servers.