locked
How to trigger workflow if new row is added to Entity - Need help with business rules RRS feed

  • Question

  • I have SQL Server 2016 MDS and creating custom workflow. I have figured out workflow part, but need to help what should be done next? I would like to call external workflow if new row is added to Entity.

    Home > My Model > Policy Entity > Business Rules > Add  (What should be done with IF? Attribute=Code, Operator=has changed?????) 

    https://docs.microsoft.com/en-us/sql/master-data-services/develop/create-a-custom-workflow-master-data-services


    Kenny_I

    Tuesday, July 4, 2017 10:58 AM

All replies

  • Hi,

    Were you able to find an answer for this? I am looking for adding the new row into Message Queue and I need to set a business rule for that.

    Thanks,

    Gayatri


    Wednesday, July 5, 2017 11:51 PM
  • The standard business rule interface does not have a way to detect just new rows. I don't know if your workflow can be executed from a stored procedure, if it can, you could do something like this:

    CREATE PROCEDURE [usr].[NewAddressesCheck] (
    	@MemberIdList mdm.[MemberId] READONLY
    	,@ModelName NVARCHAR(MAX)
    	,@VersionName NVARCHAR(MAX)
    	,@EntityName NVARCHAR(MAX)
    	,@BusinessRuleName NVARCHAR(MAX)
    	) 
    AS
    BEGIN
    SET NOCOUNT ON;
    
    DECLARE @tblNewCodes TABLE (Code NVARCHAR(250))
    
    DECLARE @LastNewAddressCheckDttm DATETIME = NULL
    		,@BusinessRuleStartDttm DATETIME = GETUTCDATE() /* MDM uses UTC dates */
    
    /* lookup the @LastNewAddressCheckDttm stored in some control table 
    
    SELECT @LastNewAddressCheckDttm = LastNewAddressCheckDttm
    FROM ...
    WHERE ...
    */
    
    INSERT @tblNewCodes (Code)
    SELECT a.Code
    FROM mdm.Addresses a WITH (NOLOCK)
    JOIN @MemberIdList ml ON a.Code=ml.Code
    WHERE a.EnterDateTime >=@LastNewAddressCheckDttm
    
    /* Do something with the codes in @tblNewCodes */
    
    /* update control table with date captured at start 
    
    UPDATE ...
    SET LastNewAddressCheckDttm=@BusinessRuleStartDttm
    WHERE ...
    */
    
    END

    You would then leave IF blank, and have THEN execute this stored procedure. The MemberIdList will be all the Entity members that need validated so it could include new and updates. You join back to the original entity and check the EnterDate against some control value to keep track of the last validation check.

    If you don't need real-time flow, you could just have a 1 minute SQL agent job that checks the entity table as well.

    Monday, July 17, 2017 6:07 PM