using trigger on one table to update another table in Oracle

by Drago Homsak
(Slovenia)

My problem is the following:


For my Oracle application, at the time when P_delivery_date changes in P_ORDERS I want to transfere P_delivery_date to S_delivery_date in S_ORDERS for coresponding records.

Tables:

purchase orders table P_ORDERS:
            P_order_number,
            P_poz_number,
            S_order_number,
            S_poz_number,
            P_delivery_date

sales orders table S_ORDERS:
            S_order_number,
            S_poz_number,
            S_delivery_date

My question is:

Is it possible in Oracle and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?

Thank you!

Not only is this possible in Oracle, it is also straightforward. All we need to to capture changes to the order date in the p_orders table and to propagate them to the s_orders table is to create a trigger on p_orders that fires every time a row is updated and compares the old and the new order dates. If they're different it updates the s_orders table with the new date(s). Such a trigger would look like this:

CREATE OR REPLACE TRIGGER RIPPLE_ORDER_DATE_UPD
AFTER UPDATE ON P_ORDERS FOR EACH ROW
BEGIN
    IF :NEW.P_delivery_date <> :OLD.P_delivery_date THEN
       UPDATE S_ORDERS
       SET S_DELIVERY_DATE = :NEW.P_delivery_date
       WHERE S_ORDER_NUMBER =:NEW.p_ORDER_NUMBER;
    END IF;
END;


This trigger will handle updates (to the order date) perfectly. What happens though with new orders? As it stands nothing, so we should probably have another trigger to propagate new orders to the s_orders table. Again this is straightforward. All that is needed is for a trigger that fires after a row is inserted and copies the relevant data to the s_orders table like so:-

CREATE OR REPLACE TRIGGER ADD_ORDER_DATE
AFTER INSERT ON P_ORDERS FOR EACH ROW
BEGIN
    INSERT INTO S_ORDERS
    (
        S_order_number
       ,S_poz_number
       ,S_delivery_date
    )
    VALUES
    (
        :NEW.S_order_number
       ,:NEW.S_poz_number
       ,:NEW.P_delivery_date
    );
END;


Now we can can handle updates to the order date and the creation of new orders. Job done. Except, perhaps not.

There are two issues with using triggers for these tasks. The first is performance - triggers are written in PL/SQL and this is much slower than SQL when doing tasks that are better done in SQL. Secondly and more importantly, triggers can be dropped or disabled which means no changes would be propagated. A better solution therefore would be to have the application explicitly update s_orders whenever new records are created in p_orders and whenever the order_date changes. That also has the beneficial side effect of making the application easier to maintain by making it more obvious what is happening and how it happens. There is a caveat though - if the s_orders table is an audit table or a history table and not really part of the Oracle application per se, then using triggers to capture changes and saving diluting the focus of the application is a good design choice.

For more details on triggers, see these questions and answers:
whats the difference between a trigger and a procedure in oracle plsql
and
whats the difference between a trigger and a constraint in oracle

You might also be interested in our PL/SQL tutorials and see our Oracle training page if you're interested in some formal training either on-line or in person.

Comments for using trigger on one table to update another table in Oracle

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 25, 2015
Rating
starstarstarstarstar
How the PL/SQL trigger know which row to update
by: Anonymous

The PL/SQL update trigger on the P_ORDERS table knows which row in S_ORDERS to update because the update statement has a where clause which uses the order number
"UPDATE S_ORDERS
SET S_DELIVERY_DATE = :NEW.P_delivery_date
WHERE S_ORDER_NUMBER =:NEW.p_ORDER_NUMBER; "

As the trigger os defined for each row, it fires once per row that is updated, so even if every row in the table was updated in one go, the update of S_ORDERS would be applied to the row in S_ORDERS corresponsing to teh row in P_ORDERS that was update

Mar 20, 2015
Rating
starstarstarstarstar
Question?
by: Oracle_newbie

This is both a very good example and explanation, thank you both. However, I would appreciate a little clarification, because I have a similar scenario that I'm working on.

I'm assuming that P_ORDERS and S_ORDERS share the same order number, and that's how the relationship is maintain between the two tables. So my question is, how exactly does the trigger know what record to update exactly, since there's no "where" clause that combines the two records? Is it implied?

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.