Can I trigger on the end of a transaction (upon commit)?
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:
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?