using update in trigger

Question

I have trigger on delete I want to delete policeman but when I delete him all other policeman that he is their boss will have null in their boss field so I used this code

create or replace trigger switch_boss
before delete
on policeman 
for each row
declare
boss number; 
begin
boss := :new.bossid;
if(:new.policemanid = :new.bossid)then
select policemanid into boss from 
(select * from policeman
order by dbms_random.value)
where rownum =1;

end if;

update policeman
set bossid = boss
where bossid = :new.policemanid;

end switch_boss;

I got error

ORA-04091: table SYSTEM.POLICEMAN is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.SWITCH_BOSS", line 13
ORA-04088: error during execution of trigger 'SYSTEM.SWITCH_BOSS'

any ideas?

UPDATE: I used compund trigger its works but not as I wanted. I wanted to set the boss of the deleted policeman as the boss for the ones he was boss of. the problem is when deleting I cant now which policemans have the deleted policem as boss. I can find them because they have null in the field after the delete but they may belong to other deleted policeman.

this is the trigger I made:

create or replace trigger switch_boss
  for delete
  on policeman 
compound trigger

  after statement is
  cursor c is select * from policeman where bossid is null for update;
  boss number;

  begin
    for r in c loop

      select policemanid into boss from 
                         (select * from policeman order by dbms_random.value)
                         where rownum =1;

      update policeman
      set bossid = boss
      where current of c;
   end loop;
  end after statement;

end switch_boss;

don't create an object(such as table) within `system` or `sys` schemas. — Barbaros Özhan, Jun 17 '19 at 19:43
[Use a compound trigger to handle this as shown here](https://stackoverflow.com/a/29491264/213136) — Bob Jarvis - Слава Україні, Jun 17 '19 at 19:47
By the way, I suggest you look at [code indentation](https://en.wikipedia.org/wiki/Indentation_style). Also, PL/SQL does not use brackets for `if` conditions as it has the `then` keyword instead. — William Robertson, Jun 17 '19 at 21:55

Tad Harrison · Accepted Answer · 2019-06-17T17:13:25.347

This is a very common problem when getting started with triggers. Oracle does not let your row-level trigger access its base table (POLICEMAN) other than through the :NEW and :OLD pseudo-records. The idea is that the table is in a state of flux, so it cannot be queried or modified by the trigger..

A typical way of handling this is to create two triggers: a row-level trigger followed by a statement-level trigger. The row-level trigger registers all of the changes in a package (packages can hold state), while the statement-level trigger follows up and applies all of the necessary modifications based on what happened in the rows--a statement-level trigger can modify the base table.

Here is a description on how to do this: Get rid of mutating table trigger errors with the compound trigger

In that article, Steven Feuerstein describes not only the traditional package-based solution to the problem, but he also provides a more modern compound-trigger-based solution.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Yes, To handle this situation, You will need a Combination of triggers as the following:

--First of all, You will need one package to hold the values:
CREATE OR REPLACE PACKAGE MY_VALUE_HOLDER AS
    POLICEMANID NUMBER;
    BOSS NUMBER;
END MY_VALUE_HOLDER;
/

-- Before each row trigger
CREATE OR REPLACE TRIGGER SWITCH_BOSS_ROW_TRG BEFORE
    DELETE ON POLICEMAN
    FOR EACH ROW
DECLARE
    BOSS   NUMBER;
BEGIN
    BOSS := :OLD.BOSSID;
    IF ( :OLD.POLICEMANID = :OLD.BOSSID ) THEN
        SELECT
            POLICEMANID
        INTO BOSS
        FROM
            (
                SELECT
                    *
                FROM
                    POLICEMAN
                ORDER BY
                    DBMS_RANDOM.VALUE
            )
        WHERE
            ROWNUM = 1;

    END IF;

    MY_VALUE_HOLDER.POLICEMANID := :OLD.POLICEMANID;
    MY_VALUE_HOLDER.BOSS := BOSS;
END SWITCH_BOSS_ROW_TRG;
/


--After statement trigger
CREATE OR REPLACE TRIGGER SWITCH_BOSS_ST_TRG AFTER
    DELETE ON POLICEMAN
BEGIN
    UPDATE POLICEMAN
    SET
        BOSSID = MY_VALUE_HOLDER.BOSS
    WHERE
        BOSSID = MY_VALUE_HOLDER.POLICEMANID;

END SWITCH_BOSS_ST_TRG;
/

The first trigger is before each row and second is after statement trigger.

DB Fiddle demo

Cheers!!

u cant do select on the table at the first trigger. because its the the table the trigger is for. and in the after statment the field change to null so u cant find the row by the old policemanid — מיכאל ביתן, Jun 18 '19 at 20:50
I saw but it doent suppose to work, I tried this but us cant run qureys of the table the trigger is for, mutation error. also it have onari relation to itself with boss field so when u delete it changes to null — מיכאל ביתן, Jun 19 '19 at 07:28

using update in trigger

2 Answers2