CODEWITHSUNDEEP
oracle-databasetriggers
getshere
getshere

Reputation: 15

raise_application_error issue in Oracle trigger

CREATE OR REPLACE TRIGGER del_info
   BEFORE DELETE
   ON emp
   REFERENCING NEW AS new OLD AS old
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   IF :old.ename = 'GAU'
   THEN
      INSERT INTO audit_table (who, dated)
           VALUES (USER, TO_CHAR (SYSDATE, 'dd-mon-yyyy hh24:mi:ss'));

      COMMIT;
      raise_application_error (
         -20001,
         'You Are not authorized to delete this record');
   ELSE
      NULL;
   END IF;
END;

There is a problem here. It works fine if I delete any user using where clause, but if I fire query like delete from emp, then it gives problems, that is it does not delete any records. The ideal behaviour should be, it should not delete that single recors(ename = 'GAU'), rest all should be deleted. Please help

Upvotes: 1

Views: 685

Answers (1)

vav
vav

Reputation: 4684

You can't accept part of the transaction and raise error on another part.

Workaround: Phoenix mode!

Once record is deleted, insert it back.

Better aproach:

  1. revoke rights to DELETE
  2. write a procedure to provide delete functionality. That procedure will always exclude "permanent" record.
  3. grant users permission to execute that procedure

3rd solution:

  1. write a view that would exclude "permanent" record. You may add WITH CHECK OPTION to prohibit insertion of another "permanent" record.
  2. grant users DELETE on that view

More on how to use/not use triggers is here

Upvotes: 1

Related Questions