Menu
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?
866
To UPDATE a table by joining multiple tables in SQL, let's create the two tables 'order' and 'order_detail. ' We can update the data of a table using conditions of other joined tables. It is possible to join two or more tables in an UPDATE query.
If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword. update Categories_Test a set (a. Description, a. Picture) = (select b.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
For instance, updating 2 different tables together in a single query/statement. This involves the use of the BEGIN TRANSACTION clause and the COMMIT clause. The individual UPDATE clauses are written in between the former ones to execute both the updates simultaneously.
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.
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
