4 Replies Latest reply: Mar 29, 2013 8:39 AM by Raj.Ogg-Oracle RSS

    OGG and firing triggers

    993741
      Hi,
      I am designing and implementing OGG to replicate a set of about 200 tables from a master system to two target systems. Updates to these tables only occur on the master system so this is a 1:n uni-directional situation. The master and target systems are all Oracle.

      Some or all of the target tables have triggers on them. For 95% of the tables, we don't want the triggers to fire when GG replicates to the table. This is easily accomplished by using the SUPPRESSTRIGGERS subparameter of the DBOPTIONS parameter in the replicat parameter file. So, I set up one extract/pump/replicat process to replicate the data to these tables.

      Of the remaining 5% of the tables, most of the time we want the triggers to fire on the target systems when data is replicated to the target table. Again this is easily accomplished by setting up another extract/pump/replicat process with DBOPTIONS NOSUPPRESSTRIGGER.

      There are a couple of tables on the target systems which have more than one trigger and we want one of the triggers to fire when replicating but not the other ones. I believe that I can partly accomplish this by putting a SQLEXEC on the MAP statement for these tables which issues an ALTER TRIGGER <schema.trigger>; DISABLE. The documentation says this SQLEXEC will run before the data is replicated to the target table so the trigger will be disabled when the replication happens.

      My problem is... How do I enable the trigger again once I have replicated data to the target table? I don't think it's possible to have a SQLEXEC run after the replication happens. Is that true?

      I have considered simply disabling the triggers when replication starts with a standalone SQLEXEC statement that runs when replication starts and enabling them using SQLEXEC ONEXIT to enable the triggers again when replication stops. this will work but I'm not sure my customer will buy this solution.

      Thanks a lot for any help you can provide.
        • 1. Re: OGG and firing triggers
          satrap
          Instead of doing all this you can put a WHEN clause in your triggers to not do anything when the change is applied by the golden gate user. For the ones you want to fire, do not use the WHEN clause

          CREATE OR REPLACE TRIGGER trig1
          AFTER DELETE OR INSERT OR UPDATE ON tab1
          FOR EACH ROW
          WHEN ( user != 'GGATE' )

          DECLARE

          BEGIN
          ---
          ---
          END;

          Edited by: satrap on Feb 27, 2013 9:25 AM

          Edited by: satrap on Feb 27, 2013 9:25 AM
          • 2. Re: OGG and firing triggers
            993741
            I've figured out how to do this. Start the replicat with DBOPTIONS SUPPRESSTRIGGERS specified. Then run a standalone SQLEXEC which executes the following stored procedure call for each trigger you want to have fire: dbms_ddl.set_trigger_firing_property(<trigger_owner> '<trigger_name>', FALSE);

            I have a set of eight triggers I want to fire so I created a simple stored procedure that executes this SP for each of my triggers. It could probably be done inline in the replicat parameter deck too.

            Also, I have a second SP which I call using SQLEXEC ON EXIT. This disables all the triggers I enabled at the beginning.

            Looking at ALL_TRIGGERS at the FIRE_ONCE column/property will tell you what state a trigger is in. Ironically, NO means the the trigger will fire and YES means it will not.
            • 3. Re: OGG and firing triggers
              Nick.W-Oracle
              Thank you for this! I'll do what I can to make sure it's included in the next set of docs. :)
              • 4. Re: OGG and firing triggers
                Raj.Ogg-Oracle
                We have a note already that mentions about set_trigger_firing_property

                     OGG Replicat executes the trigger even with SUPPRESSTRIGGERS enabled (Doc ID 1428494.1)

                Thanks,
                Rajesh