Add database triggers to automatically update registries after data propagation

We 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

If we are using using the e-Marketing Spot JSP caching technique based on activity behavior, do not add the database triggers with "WCR+CampaignInitiativeCache" invalidation. When we 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.

Task info

We must invoke an SQL file to temporarily drop the existing database triggers. After you drop the database triggers, we 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 the containers are running.

   2. Locate the 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

      WhereDB2:

      • 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.

Related tasks
Invalidating WebSphere Commerce data cache entries