Rules for creating triggers for custom tables

When you are adding SQL statements to create and drop triggers for custom tables, ensure that you follow the appropriate rules.

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:

  • DB2WC_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_convention

The trigger naming convention is a guideline to ensure that new triggers for custom tables have unique names.

DB2 WebSphere Commerce names a trigger by incrementing that last number of the last trigger in the db2/wcs.stage.trigger.sql file. Do not use 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 Ensure that the name of your new triggers follow 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.
Note: Staging triggers log the changes to database records in to the STAGLOG table. The specific record that is changed is indicated by the value of the primary key and unique index. These values are the only pieces of data that gets logged in to 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, do not change the primary key or unique index value.
Your triggers must insert records into the STAGLOG database table with the following information in the STAGLOG table columns:
  • DB2For the value of the STAGLOG.STGRFNBR column, you are recommended to use the NEXTVAL FOR STAGESEQ function to generate the value. If you use a different counter, you might result in key collisions within the STAGLOG table.
  • OracleUse the STAGESEQ.NEXTVAL function to generate the STAGLOG.STGRFNBR column value. When you are inserting a value for the STAGLOG.STGSTMP column, use the function SYSDATE to generate the time stamp value. If you use other functions to generate this value, the generated value might not result in the appropriate data format that is expected by the staging utility. If the generated value is not in the appropriate data format, data might be omitted from the staging utility.
  • For STAGLOG columns not defined directly within this table, use a value of NULL.

Primary key value insertions

When you insert primary key values into staging tables, always insert the values into the lowest numerically named columns that match the data type. The data that is generated by the staging triggers is stored in the STAGLOG database table. This data is read by the stagingprop utility, which requires primary key values to be placed in the lower numerically named columns.

Database table naming conventions

When you insert database table and column names into staging tables, use lowercase names for the database table and column names.