0
Table order 
columns - id  status
           1  confirmed 

Table trays
columns - id,orderid(fk),status
           1   1        done
           2   1        processing

I have to update the status of order table to finished only after all the elements of trays related to the particular order are done eg. one order has two trays once the status of both trays changed to done then i need to update the status of order to finished. is there anyway to handle this scenario with afterupdate trigger ? Thanks in advance

1
0

It seems that you need in this:

CREATE TRIGGER fix_order_status
AFTER UPDATE
ON trays
FOR EACH ROW
UPDATE order
SET order.status = 'confirmed'
WHERE order.id = NEW.orderid
  AND NOT EXISTS ( SELECT NULL
                   FROM trays
                   WHERE trays.orderid = NEW.orderid
                     AND trays.status <> 'done' );

DEMO

3
  • The status of trays can be multiple options like loaded,unloaded but when both turns out be done then i need to update the orderstatus is it possible to make some case conditions ? @Akina Oct 26 '21 at 21:26
  • @ManojSelvam The status of trays can be multiple options like loaded,unloaded ... and all of them are not equal to 'done', is it? so what's wrong? DEMO fiddle added.
    – Akina
    Oct 27 '21 at 4:56
  • Thanks @akina The error occured because postgres doesnt recognised tablename.column (order.status) after the set. when I changed to only column name everything worked as expected Oct 27 '21 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.