Triggers

Triggers on target tables of any replication deserve special consideration, and Dbvisit Replicate is no exception. The concern is whether triggers defined on the target tables should fire when data changes are applied to the table. This depends on the logic in the trigger and thus has to be decided by the user.

In general, triggers carried over from source database are not intended to fire, but triggers created on APPLY should fire. The reason being is that the trigger on the source that makes changes on the source will be replicated over to the target database so there is no reason for the triggers to fire again on the target. But other criteria can be considered as well, e.g. whether other tables referenced in the trigger are replicated or not. Enabled triggers are the exception to the rule.

If you are in the process of a migration, then you would disable the triggers on the target side and then when the final cutover is made you would enable them.

Variable APPLY_SET_TRIGGER_FIRE_ONCE can be used to disable or further control this functionality. If APPLY_SET_TRIGGER_FIRE_ONCE is set to YES for the apply process, then on supported versions of Oracle (10.2.0.5 and 11.2.0.2 - 11.2.0.3), the triggers do not fire for changes done by the apply process if the "set fire once" property of DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY is set for that triggers (this is the default).

For each trigger the "fire once" property can be changed for each trigger by using DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY.

On every Oracle target, you can check boolean variable DBRSAPPLY_PKG.is_dbreplicate_session to determine whether it's an APPLY session or not. Using this variable, the trigger can check whether to actually do something or not. This will require code changes in the triggers to enable this.

On every MySQL target, you can check variable @is_dbreplicate_session (valued 0/1) to find out whether it's an APPLY session or not.

On every MS SQL target, you can check context_info to find out whether it's an APPLY session (set to 1) or not.

If triggers that are created separately on the target database are not firing when DML from the Apply process is run against them, then set:

dbvrep> set APPLY.APPLY_SET_TRIGGER_FIRE_ONCE NO

For Oracle versions 11.2.0.4 and above the APPLY_SET_TRIGGER_FIRE_ONCE will not work, as Oracle has changed the licensing model. As a work around:

The workaround to is add following condition in the trigger so the trigger will not be executed when APPLY process executes DMLs.

IF DBVREP.DBRSAPPLY_PKG.is_dbreplicate_session=FALSE then
<Trigger Body>
END IF;

Example:

Following trigger populates LAST_UPDATED column whenever a row is updated.

Create or replace trigger TRG_TEMP before update on SCHEMA.TABLE for each row
Begin
:new.last_updated:=systimestamp;
end;
/

Add the trigger execution condition as follows:

create or replace trigger TRG_TEMP before update on SCHEMA.TABLE for each row
begin
IF DBVREP.DBRSAPPLY_PKG.is_dbreplicate_session=FALSE then
:new.last_updated:=systimestamp;
END IF;
end;
/