How to modify a row or statement level trigger so that it does not fire for a specific user?</ (4302092)

In a reporting or HA environment the triggers are disabled on target. In HA environment they are enabled prior to failing over to the secondary (also termed as target in this discussion). The reason is detailed in Admin Guide chapter titled "Planning SharePlex replication" section titled "Triggers". In nutshell, since the triggers are enabled on source, any DML generated by them is sent to target with the assumption that the table is replicated. If the triggers are also enabled on target, then they will result in the same DML created on target. This will result in out of sync errors on target. For this reason it is advised that the triggers be disabled in such environment. However, in a peer-to-peer environment it is required to enable the triggers. This is to let Shareplex ignore the trigger for any DML done by Shareplex user but to let the trigger fire for any other user.

There is a script named sp_add_trigger.sql that will modify ALL the triggers if invoked and the script resides in util subdirectory of the Shareplex product directory. Since it modifies ALL triggers located on that node, the users may want to control which triggers need to be modified and may want to do the modification manually since the script cannot do that. Moreover, the script only works for row level triggers and not for statement level triggers so the statement level triggers still need to be modified manually.

General information.

Shareplex's provided script "sp_add_trigger.sql" inserts the following WHEN clause into EACH trigger in the database, regardless of whether this trigger is on a replicated table:

when user != 'SharePlex_username'

However, the user may not wish to have this clause present in all triggers.  The following solution shows by means of a trigger template how to manually disable both statement and row level triggers one trigger at a time. The sp_add_trigger.sql will prompt you for entering the name of the Shareplex user. The template provided for Statement level trigger would require you to hard code the name of the Shareplex user in it.

:

A: Statement level trigger

CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE INSERT OR UPDATE on <table_name>
BEGIN

      if user != 'SharePlex_username'

then
 <rest of trigger body>;
         END IF;
      /

B: Row level trigger

CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE
INSERT ON <table_name> FOR EACH ROW
DECLARE
   <declaration>;
BEGIN
IF   user != 'SharePlex_username'

then
 <rest of trigger body>;
         END IF;
      /

Disclaimer: It is advised that the users test the manual trigger modification first in a QA environment before deploying it in a production environment, just as they would for all triggers modified automatically by sp_add_trigger.sql utility.  Moreover, this solution only provides templates and one still needs to modify the template and fill in the proper values. In case the template does not resemble the trigger, then one has to modify the trigger differently and come up with one's own way of excluding the user by the use of If or other clause. 

NOTE: Shareplex support does not provide nor troubleshoot custom scripts. Please contact Professional Services if you require Quest to provide a custom/comprehensive solution.