Administer > Stage server > Staging server utilities > Enable custom tables for staging
Rules for creating triggers for custom tables
When adding SQL statements to create and drop triggers for custom tables, follow rules.
- Trigger creation SQL file
- Trigger dropping SQL file
- Trigger naming convention
- Create trigger requirements
Trigger creation SQL file
Add SQL statements for creating triggers to the following files:
- WC_INSTALL/schema/db2/wcs.stage.trigger.sql
- WC_INSTALL/schema/oracle/wcs.stage.trigger.sql
Put the SQL statements for creating triggers for custom tables in these files.
Trigger dropping SQL file
Add SQL statements for dropping triggers to the following files:
- WC_INSTALL/schema/db2/wcs.droptrigger.sql
- WC_INSTALL/schema/oracle/wcs.droptrigger.sql
Put the SQL statements for dropping triggers for custom tables in these files.
Trigger naming convention
Trigger_naming_conventionThe trigger naming convention is a guideline to ensure that new triggers for custom tables have unique names.
WebSphere Commerce names triggers by incrementing that last number of the last trigger in the db2/wcs.stage.trigger.sql file. Avoid using this naming convention for the new triggers as the custom trigger names could conflict with any new triggers introduced by WebSphere Commerce in fix packs or later releases.
You must name the new triggers following the pattern found in the oracle/wcs.stage.trigger.sql file:
- Insert trigger: ISTG_ CustomTableName
- Update trigger: USTG_ CustomTableName
- Delete trigger: DSTG_ CustomTableName
Create trigger requirements
You must create three triggers for the custom database table:
- 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.
Your triggers must insert records into the STAGLOG database table with the following information in the STAGLOG table columns:
Staging triggers log the changes to database records into the STAGLOG table. The specific record changed is indicated by the value of the primary key and unique index, which are the only pieces of data that get logged into STAGLOG. For the staging utility to work, the values of the primary key and unique index must not be updated in an UPDATE operation (the other two operations, INSERT and DELETE, will not change the primary key or unique index value).
For STAGLOG columns not defined in this table, use a value of NULL.