using trigger on one table to update another table in Oracle
by Drago Homsak
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.
purchase orders table P_ORDERS:
sales orders table S_ORDERS:
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?
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
IF :NEW.P_delivery_date <> :OLD.P_delivery_date THEN
SET S_DELIVERY_DATE = :NEW.P_delivery_date
WHERE S_ORDER_NUMBER =:NEW.p_ORDER_NUMBER;
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
INSERT ON P_ORDERS FOR EACH ROW
INSERT INTO S_ORDERS
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
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.