Many applications require that the name and identity of the user be captured when a record is modified. Let’s create a ModifiedByID and ModifiedByName field in the Orders table that will be updated by an SQL Business Rule whenever an order is modified.
First, let’s add the columns to the table. Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Orders table node, and select Design.
Configure two new columns:
| Column Name | Data Type | Allow Nulls |
| ModifiedByID | uniqueidentifier | True |
| ModifiedByName | nvarchar(50) | True |
Save the table modifications. Switch to the web application generator, and refresh the Orders controller.
Regenerate the web application. Next, let’s remove the ModifiedBy fields from the presentation, and create a business rule to update these fields.
Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Views / editForm1 / c1 – Orders / ModifiedByUserID data field node, and select Delete option.
Confirm the operation. Right-click on Orders / Views / editForm1 / c1 – Orders / ModifiedByUserName data field node, and delete this data field as well.
Right-click on Orders / Business Rules node, and select New Business Rule.
Assign this business rule the following properties:
| Property | Value |
| Command Name | Insert|Update |
| Name | UpdatingModifiedBy |
| Type | SQL |
| Phase | After |
| Script | update Orders
set ModifiedByUserID = @BusinessRules_UserId,
ModifiedByUserName = @BusinessRules_UserName
where OrderID = @OrderID
|
Press OK to save the business rule.
Make sure to spell the business rule properties correctly – for example, if the “@BusinessRules_UserId” function was capitalized as “@BusinessRules_UserID”, the function will not be found and an exception will be thrown.
On the toolbar, press Browse to regenerate the web application.
Navigate to the Orders page, edit a record, and save.
View the record in SQL Server Management Studio. The relevant UserId and Name have been saved.
