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

Question

Hi,

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

There is an implicit new and old reference in the trigger in the form of: REFERENCING OLD AS OLD NEW AS NEW

You can write to the :NEW value but not to the :OLD value.

UPDATE Delivery set date = :new.delivery_date where id = :new.id;


CREATE OR REPLACE TRIGGER "BUR_TABLENAME" BEFORE
UPDATE ON "TABLE" FOR EACH ROW
BEGIN
  If :new.active_date is not null Then
    :new.active_date := TRUNC(:new.active_date);
End If;
END;

Template:

CREATE OR REPLACE TRIGGER TRIGGER_NAME
 BEFORE
 UPDATE
 ON TABLE_NAME
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
DECLARE
   V_VARIABLE   NUMBER (1);
BEGIN
   //Do Stuff;
  null;
end;

Answer 2

Use the OLD and NEW bind variables. OLD references the row or column being updated before the change is made; NEW references it after the change.

CREATE OR REPLACE TRIGGER trig1
    BEFORE UPDATE
    ON order  REFERENCING NEW AS new
    FOR EACH ROW
BEGIN
    UPDATE delivery
       SET ddate   = :new.ddate
     WHERE id = :new.id;
END;

You can modify the REFERENCING clause to give your bind variables different names. You can include OLD as <name> too. Example:

CREATE OR REPLACE TRIGGER trig1
    BEFORE UPDATE
    ON order REFERENCING OLD AS old_values NEW AS new_values
    ...

If you don't want to change the default names of "old" and "new", you can leave out the REFERENCING clause completely.

Answer 3

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;

Answer 4

Whenever there is a need for this kind of trigger, have a good look at your design. Is there really a need for a separate delivery record? Does an order really have more than 1 delivery ?

Triggers seem nice but they do tend to mess things up pretty quickly.