DirectScale’s Solution Provider business model requires the distinction between Client and System rules

  • Client Rules – UPSERT to client’s Database. It has the client ID in the rule name.
  • System Rules – UPSERT to Database deploy.


UPSERT Rules

  • UPSERT the rule(s) into the EventRule table including Name, Condition, and IsRerunnable.
  • UPSERT the newly-inserted rule(s) into the EventRuleOutcome table including Name, Rule, and SortOrder.

The following is an example of how to UPSERT two system rules.

-- UPSERT SYSTEM RULES INTO THE EVENTRULE TABLE

DECLARE @tempEventTable TABLE ([Name]) varchar(255), Condition varchar(1024), IsRerunnable BIT)
INSERT INTO @tempEventTable ([Name], Condition, IsRerunnable)
VALUES
('ProcessAutoShip', 'AutoShip.NextProcessDate == System.Today', i),
('ServiceExpired', 'Service.ExpirationDate == System.Today', i)

MERGE INTO EventRule AS target1
USING @tempEventTable As source1
    ON target1.[Name] = source1.[Name]
WHEN MATCHED THEN
    UPDATE SET
        target1.Condition = source1.Condition,
        target1.IsRerunnable = source1.IsRerunnable
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Name], Condition, Void, IsRerunnable)
    VALUES (source1.[Name], source1.Condition, 0, source1.IsRerunnable);

-- UPSERT MAPPINGS INTO THE EVENTRULEOUTCOME TABLE

DECLARE @templateOutcomeTable TABLE ([Name], Outcome, SortOrder)
VALUES
('ProcessAutoShip', 'ProcessAutoShip', 10),
('ServiceExpired', 'SendEmail', 10)

DECLARE @templateMappingTable TABLE (EventRuleId INT, Outcome VARCHAR(255), SortOrder INT)

INSERT INTO @tempMappingTable (EventRuleId, Outcome, SortOrder)
SELECT e.recordnumber, t.Outcome, t.SortOrder
FROM EventRule AS e
LEFT JOIN @tempOutcomeTable t ON e.Name = t.Name
WHERE e.Name IN (SELECT t.[Name]);

MERGE INTO EventRuleOutcoe AS target2
USING @tempMappingTable AS source2
    ON target2.EventRuleID = source2.EventRuleID
    AND target2.Outcome = source2.Outcome
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EventRuleID, Outcome, SortOrder)
    VALUES (source2.EventRuleID, source2.Outcome, source2.SortOrder);