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.
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?
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