CODEWITHSUNDEEP
oracle-databasetriggers
schmimla
schmimla

Reputation: 65

Update statement in a trigger or a scheduled job in Oracle?

I want to update certain rows if the status of a device is changed. Here is the plain update statement:

update REMINDER set FLAG_LIST = 0 where ID in 
(select r.id from REMINDER r 
    join DEVICE d
        on d.id = (regexp_replace(r.origin_values, '[^0-9]', ''))         
        and d.status <> 0
        and r.context = 'DEVICE'
        and r.flag_list <> 0);

I wrote a trigger like below but I get an ORA-04091 (table DEVICE is mutating, trigger/function may not see ist) when i change the status of a device:

create or replace TRIGGER DEACTIVATE_REMINDER 
AFTER UPDATE OF STATUS ON DEVICE 
for each row
BEGIN
if updating then
    if (:old.STATUS = 0 and :new.STATUS != 0) then
        update REMINDER set FLAG_LIST = 0 where ID in 
            (select r.ID from REMINDER r 
                join DEVICE d
                    on d.id = (regexp_replace(r.ORIGIN_VALUES, '[^0-9]', ''))         
                    and d.STATUS <> 0
                    and d.ID = :new.ID
                    and r.CONTEXT = 'DEVICE'
                    and r.FLAG_LIST <> 0);
    end if;
end if;
END;

Maybe i can not use a trigger here, because the same object whose modification triggered the trigger must not be modified or read in a row-level trigger? Is it better to use a scheduled job somehow?

What I also want to do is to update the FLAG_LIST back to 1, if the STATUS of a device is changed back to 0. The only way i can imagine to do this is also in the trigger, but as i wrote I`m not sure if this is basically possible.

Thank you in advance!

Upvotes: 0

Views: 219

Answers (1)

Justin Cave
Justin Cave

Reputation: 231771

A row-level trigger on device can't query the device table. My wager, though, is that you don't need to and can simply use the :new pseudorecord (note that the :new.status predicate in the subquery is redundant here given the if statement but doesn't hurt anything)

create or replace TRIGGER DEACTIVATE_REMINDER 
  AFTER UPDATE OF STATUS ON DEVICE 
  for each row
BEGIN
if updating then
    if (:old.STATUS = 0 and :new.STATUS != 0) then
        update REMINDER set FLAG_LIST = 0 where ID in 
            (select r.ID 
               from REMINDER r 
              where :new.id = (regexp_replace(r.ORIGIN_VALUES, '[^0-9]', ''))         
                and :new.STATUS <> 0
                and r.CONTEXT = 'DEVICE'
                and r.FLAG_LIST <> 0);
    end if;
end if;
END;

If that's not what you want, it would be helpful to provide a full test case that we can run to see the behavior you want. You could create a compound trigger with a row-level component that stores the modified id values in a collection and then a statement-level component that updates the table using that collection. But that seems like overkill in this situation.

Architecturally, however, I am always really hesitant about designs that put this sort of logic in triggers. It will virtually always make more sense to have a stored procedure that updates the device table and then runs whatever update you want on the reminder table. You won't need to be concerned about a mutating table exception, all the logic will be in one place rather than partially in an application and partially in a trigger, you won't encounter problems if two months from now you want to create a trigger on the reminder table that involves queries against device, etc.

Upvotes: 2

Related Questions