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. Obtain the required files to add and drop triggers.

    To retrieve the files from the development environment:

    DB2:
    • The drop triggers file is located at WCDE_installdir/schema/9.0.0.0/db2/wcs.cacheivl.drop.trigger.sql.
    • The add triggers file is located at WCDE_installdir/schema/9.0.0.0/db2/wcs.cacheivl.trigger.sql.
    Oracle:
    • The drop triggers file is located at WCDE_installdir/schema/9.0.0.0/oracle/wcs.cacheivl.drop.trigger.sql.
    • The add triggers file is located at WCDE_installdir/schema/9.0.0.0/oracle/wcs.cacheivl.trigger.sql.

    Or, to retrieve the files from the Utility server Docker container:

    1. Run docker ps to verify that your containers are running.
    2. Locate your utility container name in the NAMES column. For example, myproject_utils_1.
    3. Run docker cp to copy the appropriate files from the Utility server Docker container to your local system. 
      docker cp utility_container_name:container_file_path local_directory
      Where
      DB2:
      • The container_file_path for the drop triggers script is utilities_root/schema/9.0.0.0/db2/wcs.cacheivl.drop.trigger.sql.
      • The container_file_path for the add triggers script is utilities_root/schema/9.0.0.0/db2​​​​​​​/wcs.cacheivl.trigger.sql.
      Oracle:
      • The container_file_path for the drop triggers script is utilities_root/schema/9.0.0.0/oracle/wcs.cacheivl.drop.trigger.sql.
      • The container_file_path for the add triggers script is utilities_root/schema/9.0.0.0/oracle​​​​​​​/wcs.cacheivl.trigger.sql.
  2. Connect to the production database with a proper user ID and password.
  3. Invoke wcs.cacheivl.drop.trigger.sql against the production database.
  4. Invoke wcs.cacheivl.trigger.sql against the production database.