If you review the introduction to Email Business Rules, then you will learn that a notification will be sent out when a new customer record has been created. What if you want to trigger a notification for new customers from a certain country? Email notifications are frequently triggered in response to changes in data.  What do you do in that case?

It is time for name calling, blacklisting, and whitelisting (all in a good sense).

Let’s enhance the Northwind sample with an automatic notification that will go out when a product price has increased.

Start Project Designer, activate Controllers tab in Project Explorer, select Products data controller, and create a new SQL business rule with the following properties.

if @UnitPrice_Modified = 0 or @UnitPrice_OldValue >= @UnitPrice_NewValue
   set @BusinessRules_Blacklist = 'PriceChanged'

The business rule will blacklist something named PriceChanged if the product unit price has not increased.

Add an Email business rule with these properties.

Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true

From: "Sales Admin" <YOUR_EMAIL_ADDRESS@gmail.com>
To: RECEIPIENT@northwind.com
Subject: Price of "{ProductName}" has been increased

User {@BusinessRules_UserName} has changed the price of "{ProductName}" from {UnitPrice_OldValue, c} to {UnitPrice_NewValue, c}.

Northwind,
Administrator

Make sure to replace the highlighted parameters with your own values.

Notice the data format string “c” next to UnitPrice_OldValue and UnitPrice_NewValue. Use any standard or custom numeric, date, and time span format strings as needed.

This is the new look of Products data controller hierarchy in Project Explorer.

SQL and Email business rules are evaluated and executed in the order of their appearance in the data controller. An SQL business rule can prevent any other business rules from being executed by “blacklisting” the rule name in the parameter @BusinessRules_Blacklist. If the rule’s name is on the list, then it will not be executed. Rules without names are not effected.

Generate the application and modify a product without making any changes to the price – there will no be notifications.

Increase a price of any product and you will get a notification that may look like this.

You can have multiple “named” email business rules and implement a single SQL business rule to control their execution with the help of “blacklist”.

If you need to reorder the business rules then click on Business Rules node, select an individual rule and move it up and down.

There is also a better sounding alternative – the “whitelist”. The idea behind the whitelist is very simple. If a named business rule is not on the whitelist, they it is not allowed to execute.

This is the “whitelist” version of the SQL business rule from above.

set @BusinessRules_Whitelist = 'Nothing'
if @UnitPrice_Modified = 1 and @UnitPrice_OldValue < @UnitPrice_NewValue
   set @BusinessRules_Whitelist = 'PriceChanged'

Note that both, blacklist and whitelist, can contain multiple names separated by “comma”, or “semicolon”. For example,

PriceChanged,LowStock

Use of whitelist and blacklist depends on your personal preferences. Generally, whitelist is a more natural method of expressing notification triggers since a condition must be “positive” for a notification to go out.

Use a combination of both lists when needed.