How do you check for updates across many tables in Oracle?
oracle, triggers, view
Solution
- Can you actually create a view joining all the tables in question? Do they have foreign keys to each other?
- Assuming you can create a view, will all tables be updatable using the complex view? There are severe limitations on this.
- 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_01/server.102/b14200/statements_7004.htm#SQLRF01405
Note: I am assuming ORACLE is the db as you have tagged your question with ORACLE
Problem
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?