Adding database triggers to automatically update registries after data propagation

You can add database triggers to automatically update marketing and promotions registries after you run the stagingprop utility to publish data to the production database.

These database triggers add rows to the CACHEIVL table.
  • To automatically update the marketing registry, the "WCR+CampaignInitiativeCache" value is added to the CACHEIVL table in the DATAID column 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 scheduler job runs.
  • To automatically update the promotions registry, the following values are added to the CACHEIVL table in the DATAID column:
    • WCR+PromotionRegistry
    • WCR+PromotionGroupRegistry
    • WCR+PromotionPolicyRegistry
Note: If you are using the e-Marketing Spot JSP caching technique based on activity behavior, do not add the database triggers with "WCR+CampaignInitiativeCache" invalidation. When you use the JSP caching technique, clearing the CampaignInitiativeCache registry is not required. To set up this technique, follow the instructions in Setting up JSP snippet caching based on activity behavior.

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
      • WC_installdir/schema/db2/wcs.cacheivl.drop.trigger.sql
    • Oracle
      • WebSphere Commerce DeveloperWCDE_installdir/schema/oracle/wcs.cacheivl.drop.trigger.sql
      • WC_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
      • WC_installdir/schema/db2/wcs.cacheivl.trigger.sql
    • Oracle
      • WebSphere Commerce DeveloperWCDE_installdir/schema/oracle/wcs.cacheivl.trigger.sql
      • WC_installdir/schema/oracle/wcs.cacheivl.trigger.sql