asked on

Trigger to sync values in two columns

I am currently migrating 3000 users from one interface to another. The both interfaces will be available for a period of 6 months as deployment occurs.

The current version of the application uses a field to store a value (PER_CAPACITY), the new version stores this value in a custom field (PRV_NVALUE) found in a table that stores custom field data for multiple tables.

What I need to achieve:

Apply a trigger to the original table (R5PERSONNEL) that will update the custom field value located in the new location (R5PROPERTYVALUES).

Apply a trigger to R5PROPERTYVALUES that will update R5PERSONNEL if the value is updated.

Why I need to do this:

We have interfacing applications that will be using the value in R5PERSONNEL to generate reports. I cannot change this, that is not feasible a solution.

I tried putting a trigger on each table like the ones shown below but I am getting an error stating that the table is "mutating".

Thanks for your suggestions and time!

DROP TRIGGER D7IDEV.CHV_CUST_INSUPD;

CREATE OR REPLACE TRIGGER D7IDEV."CHV_CUST_INSUPD" 
   AFTER INSERT OR UPDATE OF prv_property, prv_rentity, prv_code, prv_nvalue
   ON d7idev.r5propertyvalues
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
WHEN (
NEW.prv_property = ('PERCAP')
      )
DECLARE
   --pragma autonomous_transaction;
   --db_error       EXCEPTION;
BEGIN
   
   IF INSERTING
   THEN
      Update d7idev.r5personnel
      set per_capacity = :NEW.prv_nvalue
      where per_code = :NEW.prv_code
      and :NEW.prv_nvalue <> per_capacity;

   
   ELSIF UPDATING
   THEN
      BEGIN
      IF :NEW.prv_nvalue <> :OLD.prv_nvalue
      THEN
      Update d7idev.r5personnel
      set per_capacity = :NEW.prv_nvalue
      where per_Code = :NEW.prv_code;
      END IF;     

      END;
   END IF;
--EXCEPTION
--   WHEN db_error
--   THEN
--      o7rae (cerrsource, cerrtype, checkresult, csqlcode);
END CHV_CUST_INSUPD;
/



CREATE OR REPLACE TRIGGER D7IDEV."CHV_PER_INSUPD" 
   AFTER INSERT OR UPDATE OF  per_capacity
   ON d7idev.r5personnel
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
WHEN (
NEW.per_capacity <> OLD.per_capacity
      )
DECLARE

BEGIN
   
   IF INSERTING 
   THEN
      INSERT into d7idev.r5propertyvalues
      (prv_property, prv_rentity, prv_class, prv_code, prv_seqno, prv_nvalue, prv_class_org, prv_updatecount, prv_created, prv_updated)
      VALUES
      ('PERCAP','PERS','*',:NEW.per_code, 1, :NEW.per_capacity, '*',1,sysdate, sysdate);

   ELSIF UPDATING
   THEN
      BEGIN
          
             Update d7idev.r5propertyvalues
              set prv_nvalue = :NEW.per_capacity
              where :NEW.per_Code = prv_code
              and prv_property = 'PERCAP'
              and prv_rentity = 'PERS';

      END;
   END IF;

END CHV_PER_INSUPD;

Open in new window

flow01

Did you define a foreign_key relation between the 2 tables. ?

If so, that the reason for this error:
within an on-row trigger on you can't reference the table the trigger belongs to and that includes
the foreign-key check that oracle is doing for you when you add a row in the other table

Solutions
1. Don't define the foreign key relation .
2. In the on-row trigger save the data (or key data) of changed row (in memory or in an independent table)
Add an on-statement trigger that modifies the table based on the saved data

johanntagle

I think there's a problem even if there's no foreign key. What I see as the problem is when an update is made on the one table, a trigger tries to update the other, which then fires a trigger to update the first table back. I'm not sure if the usual solutions for mutating triggers described in http://www.techrepublic.com/article/learn-to-avoid-the-mutating-table-problem-in-oracle/5034684, http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936, among others will help here.

My idea would be to have a 3rd table that will log the changes to be synced. Then the triggers will first check if the change to be made has already been logged there by the other table before actually inserting a new log. Then have script that will do the actual syncing. When the script updates a table, the trigger will see from the log table that this update is actually a sync update so there's no need to log anything back.

Am also curious what other experts would suggest here (if there's a more efficient or direct way).

lawrencebenson

ASKER

@flow01 - I can't change the structure because it is a vendor application and if we change the out-of-the-box objects we could be voiding our warranty/service agreement.

@johantagle - I would like to steer away from storing data in another table if possible and limit the new object to a trigger/procedure/function.

johanntagle

Well, I can't think of any other way to handle this. Maybe you can request attention for other experts.

ASKER CERTIFIED SOLUTION

lawrencebenson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lawrencebenson

ASKER

Closing this thread.