Re: How to prevent table entry from being deleted but perform statements in the body of a trigger ?

From: andrewst <member14183_at_dbforums.com>
Date: Wed, 09 Jul 2003 13:22:57 +0000
Message-ID: <3090284.1057756977_at_dbforums.com>

Originally posted by Robertgl
> Hi everybody!
>
> My problem is as following:
>
> I have a trigger which fires on delete of a table row. The body of the
> trigger checks and manipulates entries in other tables.
>
> If the entries have certain values, I would like the row of the table
> which has fired the trigger not to be deleted. As far as I understood
> this problem could be solved by a user defined exemption. But when
> using an exemption all the manipulations in the other tables are
> rolled back as well which I don't want them to do. Furthermore the
> trigger should then write a row into the table "ERRORS" which contains
> information about the kind of the error and the time it occured.
>
> Does anybody have an idea how to solve this problem?
>
> Thanks Robert
The word is "exception" not "exemption".

It is not necessarily the case that all changes will be rolled back: your application can decide how to handle the exception, e.g.

DECLARE
  my_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT( my_exception, -20010 ); BEGIN
  INSERT INTO t1(a) VALUES (1);
  BEGIN
    DELETE FROM t2 WHERE b=2;
  EXCEPTION
    WHEN my_exception THEN

• Your handler code here NULL; END; COMMIT; END;

In this example, if the BEFORE DELETE trigger on t2 had raised error ORA-20010 (by calling RAISE_APPLICATION_ERROR(-20010,'some message')) then the exception is effectively ignored (NULL statement). There is no rollback, and the insert into t1 is still committed at the end.

If the trigger needs to insert a row into the ERRORS table, such that that row still exists even if the main transaction rolls back, then you should perform the insert into ERRORS in a procedure with PRAGMA AUTONOMOUS_TRANSACTION: PROCEDURE log_error( ... )
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO errors( ... ) VALUES ( ... );   COMMIT;
END;

--
Posted via http://dbforums.com

Received on Wed Jul 09 2003 - 15:22:57 CEST