sql - Create a trigger that updates a column on one table when a column in another table is updated

Question

i have two tables

Order(id, date, note)

and

Delivery(Id, Note, Date)

I want to create a trigger that updates the date in Delivery when the date is updated in Order.

I was thinking to do something like

CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE UPDATE
ON Order
DECLARE
BEGIN
   UPDATE Delivery set date = ??? where id = ???
END;

How do I get the date and row id?

thanks

Answer 1

How do i get the date and row id?

Assuming these are columns on your ORDER table called DELIVERY_DATE and ID your trigger should look something like this:

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE ON Order
    FOR EACH ROW 
BEGIN
   if :new.delivery_date != :old.delivery_date
   then
       UPDATE Delivery d
       set d.delivery_date = :new.delivery_date
       where d.order_id = :new.id;
    end if;
END;

Note the FOR EACH ROW clause: that is necessary to reference values from individual rows. I have used an IF construct to test whether to execute the UPDATE on Delivery. If you have no other logic in your trigger you could write it like this...

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE OF delivery_date ON Order
    FOR EACH ROW 
BEGIN
   UPDATE Delivery d
   set d.delivery_date = :new.delivery_date
   where d.order_id = :new.id;
END;

---

I have answered the question you asked but, as an aside, I will point out that your data model is sub-optimal. A properly normalized design would hold DELIVERY_DATE on only one table: DELIVERY seems teh logical place for it.