Creating SQL triggers to override WebSphere Commerce database table triggers

If you need to override the SQL database table triggers that are provided for WebSphere Commerce database tables, you can create your own custom triggers to override the default triggers.

About this task

To create your own SQL triggers for provided WebSphere Commerce database tables, you must drop the existing database triggers and then create your triggers. The SQL to drop and create the triggers for the database tables must be added to the following files:
  • SolarisLinuxAIXWC_installdir/schema/dbtype/wcs.stage.trigger.sql
  • SolarisLinuxAIXWC_installdir/schema/dbtype/wcs.droptrigger.sql
  • WindowsWC_installdir\schema\dbtype\wcs.stage.trigger.sql
  • WindowsWC_installdir\schema\dbtype\wcs.droptrigger.sql
Where dbtype is the directory for your database, such as db2 or oracle.
These files contain the SQL triggers for the database tables that are provided with WebSphere Commerce. If you apply a Feature Pack to your instance, new tables can be added to your WebSphere Commerce instance. The staging triggers for the tables that are added through Feature Packs are in the following files:
  • WC_installdir/components/foundation/schema/fep{#}/{dbtype}/wcs.stage.trigger.foundation.sql
  • WC_installdir/components/foundation/schema/fep{#}/{dbtype}/wcs.stage.trigger.foundation.drop.sql
Where fep{#} is the directory for your Feature Pack level, such as fep7.

The stagingcopy utility and the stagingprop utility can run these files to drop and create or re-create triggers.

Attention: Any custom triggers that you create for database tables that are provided with WebSphere Commerce can be overwritten when you apply maintenance fixes or upgrade to a new Feature Pack or Fix Pack level.

Procedure

  1. Go to the directory that includes the SQL database triggers for the tables that are provided with WebSphere Commerce
    If you are creating custom triggers for a database table that is provided with WebSphere Commerce, go to the following directory:
    • SolarisLinuxAIXWC_installdir/schema/dbtype
    • WindowsWC_installdir\schema\dbtype
    If you are creating custom triggers for a database table that is provided with a Feature Pack, go to the following directory:
    • SolarisLinuxAIXWC_installdir/schema/fep{#}/dbtype
    • WindowsWC_installdir\schema\fep{#}\dbtype
  2. Open the SQL trigger files that you want to update for editing. Update the SQL to define your custom database triggers.
  3. DB2As an administrator with access to your database, manually run the corresponding drop trigger file. For Oracle databases, you do not need to drop the trigger before you create a new trigger.
  4. Manually run the staging trigger script to create or re-create the database table triggers to use your custom triggers.