How do you check for updates across many tables in Oracle?

Question

I have a trigger to check for a single column in a table, when there is an update (AFTER UPDATE) in that column my trigger is called and then I call a stored procedure from within my trigger in order to perform some business logic coded in Java.

So far, so good. Now things are getting more complicated, there is a new requirement that implies that the same logic (the logic executed by the trigger) should be also be performed if there are changes in columns that are in other 4 tables. I think it's not practical to have the same trigger in 5 different tables listening for different columns, and I'm not sure if I should consider creating a "view" for these columns and put a single trigger in that view (what is the cost to pay in terms of performance and/or resources?)

In your previous experience with Oracle, what have been your approach or solutions for this scenario?

Thanks!

Answer 1

The only way you can use one trigger for multiple tables is to create view.

If I didn't want to create view, I'd create a script(or whatever, use the language you want) and a list of tables on which this trigger to be created (make the creating of triggers dynamic).. I see your point is to have best maintainability on this situation and I think creating a view is cleaner solution(and easy to maintain)than dynamic creating of a trigger for each table.

Answer 2

1. Can you actually create a view joining all the tables in question? Do they have foreign keys to each other?
2. Assuming you can create a view, will all tables be updatable using the complex view? There are severe limitations on this.
3. Generally, we create only INSTEAD OF triggers on a view. There are also restrictions on triggers based on UPDATE operations. For a complete list of the issues involved refer to

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14200/statements%5F7004.htm#SQLRF01405

Note: I am assuming ORACLE is the db as you have tagged your question with ORACLE

Answer 3

Why not have your logic in a stored procedure, then just call that from the triggers?

I'd also suggest you take look at change notification, from that sound of it, thats probably what you want more so than just a plain old trigger.

Also be careful when calling external resources. Anything you call in a trigger should be transactional, as Oracle may run the trigger several times before your DML is actually completed.