Oracle Database Discussions

When trigger is compiled against DB, it compiles fine. But when the script for shareplex replication which adds a when_clause to dba_triggers is run, the trigger becomes invalid.

Surprisingly it adds when_clause to all triggers but others are not becoming invalid except new trigger. We run this script in all our databases and haven't seen this issue elsewhere.

Shareplex Script is a must for shareplex replication to work correctly that the triggers have a when clause i.e. do not fire the trigger if user=splex. Otherwise we can run into out of sync situations and it takes long time to diagnose and resolve these issues.

For now issue is resolved by creating non-problematic triggers, running sp_add_trigger script and then compiled the problem trigger and all is well. But it will lead to data sync problem (which needs to be handled carefully for fail-over)

Any pointers ?