I want to have a trigger for an update on TableA, in that trigger, part of the process is to update other rows in TableA.
What is the best way/method to do this without causing a loop.
Simple example, Table A has field "is_master" which I want only 1 record of x,y,z to have is_master = 't'. When a new row is inserted and is x,y,z (and also newer version), I want for new row to have is_master = 't', and the old row is_master = null.
The table already has a unique constraint on (x,y,z,is_master).
Somehow, I need to set old is_master to null so I can insert new row with is_master = 't';