Trigger before delete

Question

I created a before delete trigger:

create or replace trigger myTrigger3 
before delete on emp
for each row
begin
    update emp set mgr = 'Null' where mgr = :old.emp_name;
end;

Where table is

emp(emp_id integer primary key, emp_name varchar(20), mgr varchar(20))

But when I run this statement the trigger is not running.

delete from emp where emp_id = 1004;
select * from emp;

Error report -
ORA-04091: table DB20178004.EMP is mutating, trigger/function may not see it
ORA-06512: at "DB20178004.MYTRIGGER3", line 2
ORA-04088: error during execution of trigger 'DB20178004.MYTRIGGER3'

You cannot manipulate table data while the data of the table is already changing. You would have to update the table after delete statement. One way is an after delete trigger without for each row, where you update all managers no longer found in the table. Another is to use a compound trigger, where you remember all deleted IDs in the after each row section and use them in the after statement section for the updates. — Thorsten Kettner
On a side note: set mgr = 'Null' should probably be set mgr = null. You don't really want to set mgr to a string containing the word 'Null', do you? — Thorsten Kettner

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2019-11-17T15:00:23

You can prefer adding a foreign key constraint with set null option instead of such a trigger. Of course you need a primary key should already been defined on emp_id column :

alter table emp 
add constraint fk_mgr foreign key(mgr)
references emp(emp_id)
on delete set null;

Whenever you delete the record with an emp_id which has matching values with mgr column those will be emptied after deletion of the record with that emp_id.

But please prefer a data type(numeric) for mgr conforming with the column emp_id such as integer to be able to define a foreign key constraint.

Demo

By the way,

I recommend you to use soft-deletion. e.g. adding a column active to the table and set value of it to zero whenever want to delete, and do not show the records with active=0 on the application.
If you insisting on deletion do not filter by emp_name column, since there can be more than one people with the common name, but using emp_id is better by far as being unique within the table.

Trigger before delete

1 Answers