WebSphere Commerce Version 7.0.0.2 or later

Adding database triggers to automatically update registries after data propagation

You can add database triggers to automatically update the marketing registry after running the stagingprop utility to publish data to the production database. The new triggers add a row to the CACHEIVL table. The value for the DATAID column in the table is "WCR+CampaignInitiativeCache" whenever the EMSPOT or DMACTIVITY tables are modified. This value causes the DynaCacheInvalidation scheduler job to refresh the CampaignInitiativeCache registry and marketing business object cache. The cache is refreshed the next time the DynaCacheInvalidation schedular job runs.

WebSphere Commerce Version 7.0.0.3 or laterFix pack 3 and higher fix packs provide database triggers that you can add to automatically update the promotions registries after running the stagingprop utility. The new triggers add the following values to the CACHEIVL table in the DATAID column:
  • WCR+PromotionRegistry
  • WCR+PromotionGroupRegistry
  • WCR+PromotionPolicyRegistry
Feature Pack 5 or laterNote:

About this task

You must invoke an SQL file to temporarily drop the existing database triggers. After you drop the database triggers, you must invoke another SQL file to add the existing and new database triggers. To understand the syntax for invoking an SQL file, see Deploying schema changes.

Procedure

  1. From a command prompt, connect to the production database with a proper user ID and password.
  2. To drop the existing database triggers, invoke the wcs.cacheivl.drop.trigger.sql script file.
    The file is stored at the following location:
    • DB2
      • WebSphere Commerce DeveloperWCDE_installdir/schema/db2/wcs.cacheivl.drop.trigger.sql
      • WebSphere Commerce EnterpriseWebSphere Commerce ProfessionalWebSphere Commerce - ExpressWC_installdir/schema/db2/wcs.cacheivl.drop.trigger.sql
    • Oracle
      • WebSphere Commerce DeveloperWCDE_installdir/schema/oracle/wcs.cacheivl.drop.trigger.sql
      • WebSphere Commerce EnterpriseWebSphere Commerce ProfessionalWebSphere Commerce - ExpressWC_installdir/schema/oracle/wcs.cacheivl.drop.trigger.sql
  3. To add the existing and new database triggers, invoke the wcs.cacheivl.trigger.sql script file.
    The file is stored at the following location:
    • DB2
      • WebSphere Commerce DeveloperWCDE_installdir/schema/db2/wcs.cacheivl.trigger.sql
      • WebSphere Commerce EnterpriseWebSphere Commerce ProfessionalWebSphere Commerce - ExpressWC_installdir/schema/db2/wcs.cacheivl.trigger.sql
    • Oracle
      • WebSphere Commerce DeveloperWCDE_installdir/schema/oracle/wcs.cacheivl.trigger.sql
      • WebSphere Commerce EnterpriseWebSphere Commerce ProfessionalWebSphere Commerce - ExpressWC_installdir/schema/oracle/wcs.cacheivl.trigger.sql