GoldenGate

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.