Insert into a table within a trigger that raises an exception...

Can/how would you do this...

I have an before insert trigger on a table that maintains a foreign key column that the application is unaware of. Since I can't rely on the 3rd party app always sending its columns in the correct format, I wanted to check for certain errors and log them.

I test for my error conditions, raise a user defined exception and all raise_application_error. This stops the insert from occurring (good), but rolls back ANY changes that occurred during the execution of the trigger (bad) (e.g. a table which logs the invalid insert) which is what you'd expect it to do.

Is there any other way to stop the insert from occurring besides raise_application_error, that would allow the insert into my error log to happen?
-or-

Is there some other way to have what happens in a trigger NOT rolled back when an exception is raised.

Thanks
-Doug
Received on Mon Dec 01 1997 - 00:00:00 CST