trigger

[pando]

Hi

I have some problems with a trigger if anyone could help it would be great:

Well the situation is as follows:

We have two tables, one is the invoice table and the other one is like a backup of that invoice table which we will call it invoice_backup here, so when invoice table is updated the trigger will fie and update the invoice_backup. The question is I want to ensure that invoice table will always be updated even when trigger fails for any reason. It seems like if triggers fails the update operation in invoice table fails as well.
Which type of timing could be used.
We have thought creating the trigger with an exception and the exception will disable temporary the trigger and start the updating operation again on invoice table however this might work if only one session is doing the updating but could cause inconsistency if there are two or more sessions since the second session could be updating invoice table with trigger disabled.

Any help would be appreciated

cheers

[fraze]

Have you considered using materialised views (depending on what version you are running)?

[pando]

it´s 8.0.5 so we cant use materialzed views. But... what has materialized views to do with this though?

[akkerend]

If you want the update to succeed even if the trigger fails, add an exception handler with WHEN OTHERS THEN NULL;

[pando]

ops thanks, we were thinking about adding an exception but didnt know what to put!

WHEN OTHERS THEN NULL;

will probably solve our problem, will try it later today

cheers

[pwoneill]

couldn't you also just use an after update trigger? or is it not commited in the original table yet?

in any case exception handling is probablly the most robust way to go.

[pando]

we are using actually after update statement but it fails and it seems like both insert are an atomic operation if one fails other too.

[fraze]

Forgive my ignorance (my DBA experience is currently 4 days!!) but I thought you could create a materialised view on a base table that could be automatically updated every time the base table was updated. Couldn't you create invoice_backup as a materialised view and this would be updated every time the invoice table was updated?

[pando]

yea well but the developer wants two tables physically so if anything happens they lose invoice table they still have another backup, I am not sure how materilized views are built but if they are built like normal views then if we lose the base table we lose everything.... unless materialized view wont be lost

[fraze]

I dont think they would be lost - they are seperate segments within the database