Description
SharePlex provides three interactive scripts that you can run in SQL*Plus to manage triggers so that they do not interfere with replication. Review the following points before you run the scripts.
-
To run the trigger scripts, you must have access to both the SYS and DBA objects. If the SharePlex database account was set up properly during installation, that account has this access.
- After applying an Oracle patch (which sometimes creates or modifies triggers), re-run sp_add_trigger.sql on the target system if triggers are not disabled.
-
The scripts reside in the util sub-directory of the SharePlex product directory.
- The SharePlex trigger scripts support row-level triggers only. Statement-level triggers must be disabled/enabled for the SharePlex user separately.
- Triggers that modify data in tables NOT being replicated and do not affect tables in the replication configuration can fire on the target system without special treatment. However, the scripts operate on all objects in the Oracle or PostgreSQL instance, whether or not they are part of replication.
- SharePlex provides other interactive scripts for managing triggers.
- The scripts must be run for target objects. Do not run them on the source objects unless you are using peer-to-peer replication.
- The scripts should not be used if source and target objects are both on the same machine. In this configuration, the user can disable triggers on target objects.
Supported databases
Oracle and supported platforms
sp_add_trigger.sql
Use sp_add_trigger.sql when you cannot disable triggers on target objects, such as for peer-to-peer and high-availability configurations. This script changes the triggers so that they ignore the SharePlex Oracle user associated with the Post process, but fire for all other users. It inserts the following WHEN clause into each trigger in the database.
when user != ‘SharePlex_username’ begin
The script prompts for the SharePlex user name, and then it modifies the triggers. It does not modify triggers belonging to SYS, SYSTEM, and SCOTT.
Syntax
@absolute_pathname/sp_add_trigger.sql
sp_change_trigger.sql
Use sp_change_trigger.sql to replace the name of the SharePlex user in the WHEN clause that was created with sp_add_trigger.sql. It prompts for the current SharePlex user name and for the new user name. Change the SharePlex user in Oracle before you run this script.
Syntax
@absolute_pathname/sp_change_trigger.sql
sp_remove_trigger.sql
Use sp_remove_trigger.sql to remove the WHEN clause created with sp_add_trigger.sql. It prompts for the SharePlex user name and then automatically removes the WHEN clause from all of the triggers. Use this script if you no longer will be replicating to those tables. If you continue to use SharePlex to replicate to those tables without disabling the triggers, the triggers will fire when SharePlex posts data, and SharePlex will generate out-of-sync errors.
Syntax
@absolute_pathname/sp_remove_trigger.sql
Trigger Scripts for PostgreSQL
Description
SharePlex provides three interactive scripts, that you can run in SQL*Plus to manage triggers so that they do not interfere with replication.
Review the following points before you run the scripts:
-
To run the trigger scripts, you must have access to both the SYS and DBA objects. If the SharePlex database account was set up properly during installation, that account has this access.
- After applying a PostgreSQL patch (which sometimes creates or modifies triggers), re-run sp_pg_add_trigger.sql on the target system if triggers are not disabled.
-
The scripts reside in the util sub-directory of the SharePlex product directory.
- The SharePlex trigger scripts support row-level triggers only. Statement-level triggers must be disabled/enabled for the SharePlex user separately.
- Triggers that modify data in tables NOT being replicated and do not affect tables in the replication configuration can fire on the target system without special treatment. However, the scripts operate on all objects in the PostgreSQL instance, whether or not they are part of replication.
- SharePlex provides other interactive scripts for managing triggers.
- The scripts must be run for target objects. Do not run them on the source objects unless you are using peer-to-peer replication.
- The scripts should not be used if source and target objects are both on the same machine. In this configuration, the user can disable triggers on target objects.
Limitation: As the PostgreSQL database has a one-to-many relationship between users and schemas, i.e., one user and many schemas, these scripts will take the user as input and enable or disable all triggers under that user's database.
Supported databases
PostgreSQL on supported platforms
sp_pg_add_trigger.sql
Use sp_pg_add_trigger.sql when you cannot disable triggers on target objects, such as for peer-to-peer and high-availability configurations. This script changes the triggers so that they ignore the SharePlex PostgreSQL user associated with the Post process, but fire for all other users. It inserts the following WHEN clause into each trigger in the database.
when user != ‘SharePlex_username’ EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
The script prompts for the SharePlex user name, and then it modifies the triggers. It does not modify triggers belonging to SYS, SYSTEM, and SCOTT.
Syntax
\i absolute_pathname/sp_pg_add_trigger.sql
sp_pg_change_trigger.sql
Use sp_pg_change_trigger.sql to replace the name of the SharePlex user in the WHEN clause that was created with sp_pg_create_trigger.sql. It prompts for the current SharePlex user name and for the new user name. Change the SharePlex user in PostgreSQL before you run this script.
Syntax
\i absolute_pathname/sp_pg_change_trigger.sql
sp_pg_remove_trigger.sql
Use sp_pg_remove_trigger.sql to remove the WHEN clause created with sp_pg_create_trigger.sql. It prompts for the SharePlex user name and then automatically removes the WHEN clause from all of the triggers. Use this script if you no longer will be replicating to those tables. If you continue to use SharePlex to replicate to those tables without disabling the triggers, the triggers will fire when SharePlex posts data, and SharePlex will generate out-of-sync errors.
Syntax
\i absolute_pathname/sp_pg_remove_trigger.sql
Database Setup Utilities
The Database Setup utilities automatically configure a source or target database to allow SharePlex connections and to establish required database components that support replication.
For detailed information on how to set up various database utilities, refer to the Database Setup Utilities section in the SharePlex Installation and Setup Guide.
SharePlex for Oracle Cloud Infrastructure
