Rules
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);
Updated over 3 years ago