[SQL] Update ‘THIS’ row in a trigger function
I have a table that has a column by where if the value of that column changes to 'C' – then the value of different column (but the same row) on the same table should change to 0.
Here is my table
| numseats | status | --------------------- | 4 | R | | 3 | R | | 9 | R | | 4 | R |
So when R changes to C – numseats in that row should change to 0.
Here is my trigger and function
CREATE OR REPLACE FUNCTION removeSeats() RETURNS trigger AS $$ begin IF pg_trigger_depth() <> 1 THEN RETURN NEW; END IF; UPDATE flightbooking set OLD.numseats = 0; return new; END; $$ LANGUAGE plpgsql; CREATE TRIGGER removeseats AFTER UPDATE on flightbooking FOR EACH ROW WHEN (flightbooking.status = 'C') EXECUTE PROCEDURE removeSeats();
I get the following error
ERROR: invalid reference to FROM-clause entry for table "flightbooking"
However even if I get past that bit and actually call the function then it will set all values in numseats to 0… Where I just want ''THIS''.numseats to 0. I tried using OLD.numseats but that threw an error saying OLD did not exist.
Submitted April 30, 2017 at 06:34PM by PM_Me_Compliments
via reddit http://ift.tt/2oNaOXA