This content has been marked as final. Show 3 replies
why don't you just update the correct rows the first time around?
A trigger should take the identifier value of that particular id and update all
Well that isn't going to work. You have basically stated the problem as
When any one row is updated a special way the trigger fired by the update should update all other rows in the table that have certain values.
A trigger is fired as part of a transaction and in some cases may be fired multiple times for the same event. A row level trigger has no way of knowing what other rows were updated before it fired for this new row or what other rows might be updated after if fires for this new row.
So even if the trigger fires for row 2 and tries to update row 3 it could find that row 3 had already been updated by this transaction and would want to update it again. Look up MUTATING TABLE on the net to see why you can't do this.
Also if the trigger fired for row 2 and was able to update another row (perhaps row 1) the same transaction could later update row 1 and wipe out the change that the row 2 trigger had just made.
That type of procedural model is dysfunctional. A trigger can't be used this way.
What is the meaning of that table and it's columns?
It looks like this table is not in third normal form...
If you can't change the table-structure, then can you instead of having your application-code issue an update statement against this table, have your application-code execute a stored procedure? This stored procedure would have two IN parameters: ID and USER_ID. And inside the stored procedure you would code out how to update the multiple rows.
Edited by: Toon Koppelaars on Feb 27, 2012 5:40 AM
Or maybe you can just change the update-statement that your application-code issues into this:
Still much better than trying to develop triggers to do what you stated...
update table1 T1 set T1.user_id = 'SUPER_USER', T1.set_value = 'U' where T1.identifier in (select T2.identifier from table1 T2 where T2.id = 1);