freshgrill
asked on
Postgres Update Triggers (an update in an update)
Postgres 9.0
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';
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';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
update all is_master to null
insert new row with is_master ='t'
surround this with a transaction + COMMIT / ROLLBACK and you done....