Rules for creating triggers for custom tables
When adding SQL statements to create and drop triggers for custom tables, ensure that you follow rules covering the following areas:
- 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:
(DB2) WC_installdir/schema/db2/wcs.stage.trigger.sql
(Oracle) WC_installdir/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:
(DB2) WC_installdir/schema/db2/wcs.droptrigger.sql
(Oracle) WC_installdir/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.
(DB2) 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 your new triggers as your custom trigger names could conflict with any new triggers introduced by WebSphere Commerce in fix packs or later releases.
(Oracle) You must name your 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 STAGLOG table. The specific record changed is indicated by the value of a unique key, which is the only piece of data that gets logged into STAGLOG. For the staging utility to work, the value of the unique key must not be updated in an UPDATE operation (the other two operations, INSERT and DELETE, will not change the unique key value).
For STAGLOG columns not defined in this table, use a value of NULL.
Related concepts
Related tasks
Enabling custom tables for staging
Create triggers for custom tables
Related Reference