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

**RobertGl** · Jun 27 '08, 04:53 PM

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

andrewst <member14183@db forums.comwrote in message news:<3090284.1 057756977@dbfor ums.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_APPLICATI ON_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_TRAN SACTION:
>
PROCEDURE log_error( ... )
IS
PRAGMA AUTONOMOUS_TRAN SACTION;
BEGIN
INSERT INTO errors( ... ) VALUES ( ... );
COMMIT;
END;

Hi Andrew!

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

Robert

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

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

Comment