Create triggers for custom tables

If you have created new tables to be staging-enabled, 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:

    • Retrieve the files from the development environment.

      • The drop triggers file is located at

      • The add triggers file is located at

      Where dbtype is "db2" or "oracle".

    • Retrieve the files from the Utility server Docker container.

      1. Run docker ps to verify that the containers are running.

      2. Locate the 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

      • The container_file_path for the add triggers script is

      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 our custom table does not have uniquely identifiable rows within these restrictions, modify our 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. We 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.


Related concepts
Staging environment


Related tasks
Create a staging environment


Related reference
Staging trigger example
Rules for creating triggers for custom tables
Customized database table requirements