Unanswered: triggers for checking to see if changes occur to tables during update
Our previous developer was unable to figure this one out and as a result I am in charge of completing the project.
I am creating a series of database triggers to check to ensure that if an UPDATE occurs against a base table, that logic is included within the triggers to check for a net change against the data in these base tables. How can I do this? We are using Oracle 9i database on Solaris. Would I need to include an autonomous transaction within my trigger to do this successfully? Thanks!
You almost certainly don't want an autonomous transaction (which can't even see what you have done), but I'm not clear on what you are trying to do, or exactly what you mean by "net change".
If you want to inspect the data before and after the update statement, then you can use a BEFORE UPDATE and an AFTER UPDATE statement-level trigger to do so. Or you can save information about each individual row update using a FOR EACH ROW trigger and a packaged collection, and then process the saved information in an AFTER statement-level trigger.