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

[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

[RobertGl]

andrewst <me*********@dbforums.com> wrote in message news:<30****************@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