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.

Procedure

  1. Obtain the required files to add and drop triggers by performing one of the following actions:
    1. Retrieve the files from the development environment.
      • The drop triggers file is located at WCDE_installdir/schema/9.0.0.0/dbtype/wcs.cacheivl.drop.trigger.sql.
      • The add triggers file is located at WCDE_installdir/schema/9.0.0.0/dbtype/wcs.cacheivl.drop.trigger.sql.
      Where dbtype is "db2" or "oracle".
    2. Or, retrieve the files from the Utility server Docker container.
      1. Run docker ps to verify that your containers are running.
      2. Locate your utility container name in the NAMES column. For example, myproject_utils_1.
      3. Run docker cp to copy the appropriate files from the Utility server Docker container to your local system.
      docker cp utility_container_name:container_file_path local_directory
      Where
      • The container_file_path for the drop triggers script is utilities_root/schema/9.0.0.0/dbtype/wcs.cacheivl.drop.trigger.sql.
      • The container_file_path for the add triggers script is utilities_root/schema/9.0.0.0/dbtype​​​​​​​/wcs.cacheivl.drop.trigger.sql.
      Where dbtype is "db2" or "oracle".
  2. Review the STAGLOG table information. Use the STAGLOG table information and this topic to create the required triggers for the custom table.
  3. 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.
  4. 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.
  5. Ensure that your triggers follow all rules for creating triggers for custom tables.
  6. 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;
  7. Put SQL statements to drop the three triggers in the wcs.droptrigger.sql file.