Creating triggers for custom tables

If you have created new tables that you want to be staging-enabled, you must add SQL statements to create and drop triggers for the new table to the following files:

  • DB2WC_installdir/schema/db2/wcs.stage.trigger.sql
  • DB2WC_installdir/schema/db2/wcs.droptrigger.sql
  • OracleWC_installdir/schema/oracle/wcs.stage.trigger.sql
  • OracleWC_installdir/schema/oracle/wcs.droptrigger.sql

Procedure

  1. Review the STAGLOG table information. Use the STAGLOG table information and this topic to create the required triggers for the custom table.
  2. Gather the following information about the custom table:
    • table name
    • table scope (site, merchant, or mixed site and merchant)
    • primary or unique key columns

      Rows in staging-enabled tables rows must be uniquely identifiable by at most five columns: Two columns containing strings (maximum length: 254 characters) and three columns containing numbers (maximum length: BIGINT). If your custom table does not have uniquely identifiable rows within these restrictions, modify your custom database table to meet these criteria.

  3. Put SQL statements to create three triggers for the custom database table in the wcs.stage.trigger.sql file. You must create three triggers:
    • An INSERT trigger to capture insert operations on the custom table.
    • An UPDATE trigger to capture update operation on the custom table.
    • A DELETE trigger to capture delete operations on the custom table.
  4. Ensure that your triggers follow all rules for creating triggers for custom tables.
  5. Manually run the wcs.stage.trigger.sql script file to generate the triggers on the relevant table.
    Note: To verify the triggers defined, you run the following SQL statements to query the system table ALL_TRIGGERS:
    select * from all_triggers;
  6. Put SQL statements to drop the three triggers in the wcs.droptrigger.sql file.