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

From: RobertGl <robert_at_gliss-mann.de>
Date: 10 Jul 2003 05:43:03 -0700
Message-ID: <1b37c0d4.0307100443.599f2610_at_posting.google.com>


andrewst <member14183_at_dbforums.com> wrote in message news:<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;
Hi Andrew!

Yes, the PRAGMA AUTONOMOUS_TRANSACTION did it!! Thanks a lot for helping out so quickly.

Robert Received on Thu Jul 10 2003 - 14:43:03 CEST

Original text of this message