I created a trigger to raise an error if the deleted row have the datefin field that didn't exceeded a year

Question

Here is my code:

create or replace trigger tr_interdit
before delete on reservation
for each row
DECLARE
    V_res_date RESERVATION.DATEFIN%type;
begin
    SELECT DATEFIN into V_res_date
    FROM reservation
    where DATEFIN = :old.DATEFIN;

    if V_res_date<add_months(V_res_date,-12)
        then RAISE_APPLICATION_ERROR(-20001, 'Date fin na pas depasse un ans');
    end if;

end tr_interdit;
/

But I'm getting this error when I delete a row from reservation after being deleted even though it should not be.

Here is the error:

DELETE FROM "DANIEL"."RESERVATION" WHERE ROWID = 'AAAFCvAABAAALHhAAA' AND ORA_ROWSCN = '3392006' and ( "NUMR" is null or "NUMR" is not null ) ORA-04091: table DANIEL.RESERVATION is mutating, trigger/function may not see it ORA-06512: at "DANIEL.TR_INTERDIT", line 4 ORA-04088: error during execution of trigger 'DANIEL.TR_INTERDIT'

why you are selectring from table? there is changed value in :new.datefin — Ayubxon Ubaydullayev
because i want to get the value of the selected row to be deleted — daniel
Your IF condition does not make sense. V_res_date<add_months(V_res_date,-12) will never be true. — a_horse_with_no_name

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2019-01-15T07:18:11

No need for a SELECT, just access the old value directly.

Your condition v_res_date < add_months(v_res_date,-12) will never be true.

I assume you do not want to use v_res_date in the add_months() call, but compare the value of :old.DATEFIN with the "current date" - sysdate

create or replace trigger tr_interdit
before delete on reservation
for each row
begin
    if :old.DATEFIN < add_months(sysdate,-12)
        then RAISE_APPLICATION_ERROR(-20001, 'Date fin na pas depasse un ans');
    end if;

end tr_interdit;
/

I created a trigger to raise an error if the deleted row have the datefin field that didn't exceeded a year

1 Answers