Oracle “new or old reference not allowed in table level trigger”

oracleplsqltrigger

I have this table

CREATE table Pharmacists (
PhFname varchar2(10),
PhLname varchar2(10),
Salary number(7),
Nationality varchar2(10),
Shift_Start varchar2(2),
Shift_End varchar2(2),
PhID number(7) primary key,
Super_ID number(7) ,
bdate date,
constraint super_fk foreign key (Super_ID) references Pharmacists (PhID) on delete cascade);

I tried to create this trigger

create or replace trigger updShE
after update of Shift_Start on Pharmacists
begin
update Pharmacists
set Shift_End=to_number(:new.Shift_Start, '99') +8 
where PhID = :New.PhID ;
end;

but this error appears

new or old reference not allowed in table level trigger

Best Answer

You need a row before level trigger for that.

And you don't need an update, just assign the new value:

create or replace trigger updshe
  BEFORE update of Shift_Start on Pharmacists
  FOR EACH ROW
begin
  :new.shift_end := to_number(:new.Shift_Start, '99') + 8;
end;
/

Related Solutions

Skip running trigger during cascade delete

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place. It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y. Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.

NOT getting a mutating table error in trigger

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

Best Answer

Related Solutions

Skip running trigger during cascade delete

NOT getting a mutating table error in trigger

Related Question