Rules for creating triggers for custom tables

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

• Trigger creation SQL file

• Trigger dropping SQL file

• Trigger naming convention

• Create trigger requirements

• Primary key value insertions

• Database table naming conventions

Trigger creation SQL file

In the Utility server Docker container, add SQL statements to create triggers in the utilities_root/schema/9.0.0.0/db2/wcs.stage.trigger.sql file.

Put the SQL statements for creating triggers for custom tables in this file.

Trigger dropping SQL file

In the Utility server Docker container, add SQL statements to drop triggers to file utilities_root/schema/9.0.0.0/db2/wcs.droptrigger.sql.

Put the SQL statements for dropping triggers for custom tables in these files.

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 our new triggers as our 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

We 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:

• (DB2) For the value of the STAGLOG.STGRFNBR column, we are recommended to use the NEXTVAL FOR STAGESEQ function to generate the value. If we use a different counter, we might result in key collisions within the STAGLOG table.

• (Oracle) Use the STAGESEQ.NEXTVAL function to generate the STAGLOG.STGRFNBR column value. When we 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.

Related tasks
Creating triggers for custom tables