Create a Timestamp Trigger: Step 2

After the timestamp column has been created, create a trigger that records the timestamp when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the timestamp method.

The Execute DDL feature enables you to create a database object from within the replication interface. After you have created the object, add the object to your master group, to ensure that the object is created at all master sites.

Complete the following steps to create a timestamp trigger:

1. Complete the steps in Before Configuring Conflict Resolution.

2. Complete the steps in Configuring Timestamp Resolution.

3. Return to the Master Groups page.

4. Select the target master group that contains the object for which you want to add the timestamp trigger.

5. Click the number in the Administrative Request column.

   The Administrative Requests page appears.

6. Click Advanced to display, if required, the Enter DDL Text field.

7. Enter the DDL text in the Enter DDL Text field to create the timestamp trigger.

   For example, suppose you had the following conditions for the trigger:

   • You want to call the trigger ORD_TRIG.

   • The trigger is in the OE schema on the ORDERS table.

   • The timestamp column name is TIME_STAMP.

   Given these conditions, enter the following to create the trigger:

   CREATE TRIGGER oe.ord_trig
        BEFORE
           INSERT OR UPDATE ON oe.orders FOR EACH ROW
        BEGIN
           IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
             :NEW.TIME_STAMP := SYSDATE;
           END IF;
        END;
   

   Note: In case of timestamp replication for materialized views, you must ensure that the trigger calls DBMS_MVIEW.I_AM_A_REFRESH so that the trigger does not fire as a result of a refresh operation.

8. From the Execute At list, select the master sites where you want to create the object.

   Because you are replicating this trigger, you can simply select the master definition site. When you add this trigger to your master group, it is created automatically at all master sites participating in the master group.

9. Click Execute.

10. Return to Master Groups page.

11. Click Edit.

    The Edit Master Group page appears.

12. Click Objects.

    The Objects subpage of the Edit Master Group page appears.

13. Click Add.

    The Add Objects page appears.

14. Select Trigger from the Object Types list and select the schema in which you created your trigger from the Schema list.

15. Click OK.

    The Objects subpage of the Edit Master Group page reappears.

16. Locate and select the trigger that you just created from the Name list.

17. Select Use Existing Object for the new trigger if the trigger exists at the other master sites. In this case, the existing trigger is used at the other master sites.

    If a trigger of the same name exists but is not exactly the same and the Use Existing Object box is enabled, you receive an error when adding the trigger to your master group.

    If the trigger does not exist at the other master sites, the trigger is created at the other sites regardless of this box setting.

18. Select Copy Row Data for the new trigger.

19. Click Apply.