before triggers on dodgy insert

[myersg]

we seem to have a bug in our code that is allowing some null columns to be inserted into a table. However those columns have NOT NULL constraints on them and thus the insert fails.

We're trying to track down the circumstances of the event and the programmer tasked with this came up with the idea of a before trigger on the table so catch the offending insert and insert the offending bind variables into a temp table. We dont want to add debug lines of code to the app and send it out.

However because the actual insert isn't happening because of the not null constraint the before trigger doesn't fire.

We tried a instead of trigger on a view of the table but that doesn't work either.

any ideas how we can catch the insert parameters as its happening without resorting to amending the app?

Its not realistic to turn on sql trace either as the app lives on the server not on each client so the sqlnet.log file will be across all users of the app making it unreadable

 

[sem]

I can't understand why before insert trigger doesn't suit you? Is it due to subsequent rollback? In this case you may use either autonomous transaction or some kind of asynchronous processing (pipe, queue)

Regards, Dima

 

[myersg]

the trigger does not fire becuase the constraints seem to be checked before the insert is performed. the constraint fails and so the insert never occurs and thus the trigger is never fired.

a before trigger is exactly what we want but it only seems to work if the db decides it *will* insert a row.

 

[sem]

No. Before-insert trigger fires BEFORE constraint validating. Try to create it again and be more careful.

Regards, Dima

 

[jee]

It sounds like you are not understanding Dima's response.
Your trigger probably is firing, but because the constraint failure causes a rollback, you see no results in your temporary table.

What you need to do is to write to the temporary table inside an autonomous transaction, which can be committed independently of the triggering transaction. I do this all of the time to record errors in an error log table. If you don't know how to create an autonomous transaction, it is discussed in the Oracle documentation (try the Application Developer's Guide).

If you can't use autonomous transactions (because you are in a distributed transaction, for instance), you can use an Oracle Pipe or advanced queue to send the data to a task that can record the data.

 

[myersg]

thanks for the clarification. I've got the programmer to stick some dbms_output.put_lines at various points in the trigger to prove its firing (or not)

I'll get the programmer to rtfm on autonomous transactions too.


cheers