DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: trigger

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Have you considered using materialised views (depending on what version you are running)?

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it´s 8.0.5 so we cant use materialzed views. But... what has materialized views to do with this though?

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    If you want the update to succeed even if the trigger fails, add an exception handler with WHEN OTHERS THEN NULL;

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    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?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    I dont think they would be lost - they are seperate segments within the database

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width