Oracle before delete trigger-CodePudding

I have two tables

army

name VARCHAR(50) PRIMARY KEY
number_of_soliders INTEGER

soldier

soldier_id INTEGER PRIMARY KEY
army_name VARCHAR(50) REFERENCES army(name)

Now, I need to create trigger, which would work as ON DELETE CASCADE. The problem is, that I have already existing trigger for soldier, which automatically updates number_of_soldiers for his army and if i just put there ON DELETE CASCADE, it will result in a error, because when I delete the army, the trigger can't reference the number_of_soliders back.

So the task is to create trigger, which will delete all soldiers of the army before the army itself is deleted. I have tried:

CREATE OR REPLACE TRIGGER TRG_DELETE_SOLDIER
    BEFORE DELETE ON army
        FOR EACH ROW
BEGIN
    DELETE FROM soldier WHERE army_name = :old.name;
END;

Hovewer, this results in an error table is mutating, trigger may not see it on table operates, M:N table which references Soldier ID (ON DELETE CASCADE) and Weapon ID. However, the trigger does not touch this table, nor the second one for updating number_of_soldiers.

Any ideas what could be wrong?

CodePudding user response：

May I suggest to use on delete cascade with your foreign keys on your tables instead of solving this with a trigger?

alter table soldier 
add constraint fk_soldier
  foreign key (army_name)
  references army(name)
  on delete cascade
;

If you already use on delete cascade on this constraint, then maybe you forgot it on the weapon_id, which you also mention in your question.

NOTE 1: please consider using a numerical primary key for your army table and the foreign key. You are up to big trouble if you want to change the army name and need to update all child tables as well.

NOTE 2: you don't have to store the number of soldiers in your army table because you can let the database calculate that. Do you have a specific reason to store it redundantly?

CodePudding user response：

Storing and maintaining number_of_soldiers in the parent table is awkward and can break if multiple sessions insert/delete soldiers for the same army at the same time. If you don't do that then you don't need soldier-level triggers to update the army table, and can go back to ON CASCADE DELETE without a trigger on the army table either. The number of soldiers for each army can be found by joining the tables:

select a.name, count(s.soldier_id) as number_of_soldiers
from army a
left join soldier s on s.army_name = a.name
group by a.name;

db<>fiddle demo. (That query can of course be stored as a view; which could be materialized if necessary.)

But for the sake argument, if you are stuck with the table column maintenance and need the trigger to clean up, an option is to selectively bypass the soldier-level trigger, using a when clause and an environment context value.

For example, you could add calls to dbms_application_info.set_client_info (or module, or action - whatever make most sense), with a value that makes sense for you to indicate why the soldier-level trigger should not fire; something like (with a not-very-helpful value):

CREATE OR REPLACE TRIGGER TRG_DELETE_SOLDIER
    BEFORE DELETE ON army
        FOR EACH ROW
BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO('CASCADING');
    DELETE FROM soldier WHERE army_name = :old.name;
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null);
END;
/

Then in the soldier-level trigger test for that value:

CREATE OR REPLACE TRIGGER trg_decrement_army
BEFORE DELETE ON soldier
FOR EACH ROW
WHEN (sys_context('userenv', 'client_info') is null
   OR sys_context('userenv', 'client_info') != 'CASCADING')
BEGIN
  UPDATE army SET number_of_soliders = number_of_soliders - 1
  WHERE name = :old.army_name;
END;
/

If the context value has been set by the army-level trigger, then soldier-level trigger won't run because the when condition is not met. Otherwise it will still run and decrement the soldier count.

db<>fiddle

But this is still not recommended...