Business Rule Extension

来源:互联网 时间:1970-01-01

Applies To: SQL Server 2016 Preview

Business rule is a rule that you use to ensure the quality and accuracy of your master data. You can use a business rule to automatically update data, to send email, or to start a business process or workflow. The system only provides a certain number of pre-defined conditions and actions. We are introducing Business Rule Extension in SQL Server 2016, which enables customers to define more flexible and powerful Business Rules. Users can create their own rules for both conditions and actions.

Create your own conditions

SQL functions that match following criteria can be used as Business Rule conditions.

Must be defined under [usr] schema. The return value type must be BIT. Only following types are supported for parameter types. NVARCHAR DATETIME2 DECIMAL (precision, scale) precision must be 38 scale must be a value from 0 to 7

Example

CREATE FUNCTION [usr].[IsDateEmpty]

(

@Value DateTime2

)

RETURNS BIT

AS BEGIN

SET @Value = NULLIF(LTRIM(RTRIM(@Value)), N'')

IF @Value IS NULL

BEGIN

RETURN 1

END

RETURN 0

END

Create your own actions

SQL stored procedures that match following criteria can be used as Business Rule actions.

Must be defined under [usr] schema. Only contain following parameters @MemberIdList mdm.[MemberId] READONLY @ModelName NVARCHAR(MAX) @VersionName NVARCHAR(MAX) @EntityName NVARCHAR(MAX) @BusinessRuleName NVARCHAR(MAX)

Example

CREATE PROCEDURE [usr].[SetCurrentDate]

(

@MemberIdList mdm.[MemberId] READONLY,

@ModelName NVARCHAR(MAX),

@VersionName NVARCHAR(MAX),

@EntityName NVARCHAR(MAX),

@BusinessRuleName NVARCHAR(MAX)

)

AS BEGIN

INSERT INTO stg.Product_Leaf (ImportType, BatchTag,Code,CreatedDTM)

SELECT 0, N'BR', Code, GETDATE()

FROM @MemberIdList

EXEC[stg].[udp_Product_Leaf]

@VersionName = @VersionName,

@BatchTag = N'BR'

END

Script actions will be executed as mds_br_user which has following permissions

Schema

Permissions

mdm

SELECT

stg

SELECT, UPDATE, DELETE, EXECUTE, INSERT

usr

FULL

Create a Business Rule with script conditions and script actions

Let’s create a business rule against Product entity that updates the CreatedDTM to current date if the value is not set. You can get the Product entity from Sample Packages .

Navigate to Business Rule Manage page after create script condition and action using sample code above. Choose Product model and Product entity and then click Add Button, now a new empty Business Rule is created.

Then click Edit button, we can find IsDateEmpty and SetCurrentDate are shown on the components panel.

Now let’s drag “ IsDateEmpty” to IF panel on the right, and then drag CreatedDTM from Attributes Panel to Edit Condition Panel. Click Save button to save the condition.

In order to create the action, just drag SetCurrentDate to Then panel and click save on the Edit Action Panel.

After creating the Business Rule, click top left Back button to go back to the management page and click “Publish business rules” button to activate the rule.

Now let’s test the Business Rule we just created.

1. Choose Product model and an open version in the main page

2. Click Explorer Task button.

3. Choose Product entity from Entities menu.

4. Click Marketing attribute group

5. Click Add Member and enter “BB-1111” in Code field.

6. Click Ok button

7. Now we can see the CreatedDTM for Product “BB-1111” is set to current date.



相关阅读:
Top