Can I trigger on the end of a transaction (upon commit)?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Can I trigger on the end of a transaction (upon commit)?

  1. #1
    Join Date
    Aug 2003
    Location
    Southern Jersey
    Posts
    16

    Can I trigger on the end of a transaction (upon commit)?

    Hey everyone,

    I am working on a project who's duties are to extract information from our database based off of when table changes take place. I am going to use triggers as it seems to be the most logical method of signaling a change in the table. However, I am running against a problem that I think will be a performance hog and I need a way to limit the execution of the triggers further.

    Basically, I have a row level trigger which will feed an updating_tracking table. It will give me rowids for each row that was updating/inserted/or deleted. Now that table will sit there and keep track of the changes, but I need a way of signaling my application when information is DONE being added to that table.

    I tried implemented row and table level triggers, (the row level to fill the table for each for, and the table level to fire off when it's done). This works, but the problem is that the table level trigger will fire a lot more than I'd like it to.

    The problem with the table level trigger is that it will call a shared library routine to signal my application that it is time to read from the update tracking table and gather all the changes made. However, if a user (actually another application, and it does several hundred of these before doing a commit) does the following:

    update ...;
    insert ...;
    delete ...;
    commit;

    I'll get a table trigger for each of those statements. I only want to be alerting the application when a commit is done since alerting the application for every single statement could get pretty time consuming.

    Is this possible? I can't seem to find a trigger that will do it, maybe off a system table that tracks commits? I don't know...someone?

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Raise a error before commit statement (or after as per your requirement) in a begin end block by which you can fire the trigger.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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