Gaining/Losing DKP through Raid Value Changes
CREATE TRIGGER [dbo].[tss_Raids_UPDATE]
ON [dbo].[tss_Raids]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted)
RETURN;
/*
For the records where Active does not change
and Active = True, update all other fields.
*/
...
/*
If the RaidValue has changed, update the DKP for all Players
that attended that event. This is for Raids not marked as inactive.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP + checker2.fncRaidValueDiff,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker2.fncRaidValueDiff
FROM (
SELECT checker.fncRaidValueDiff,
tss_Rosters.PlayerID
FROM (
SELECT (inserted.RaidValue - deleted.RaidValue) AS fncRaidValueDiff,
inserted.RaidID
FROM inserted
INNER JOIN deleted ON inserted.RaidID = deleted.RaidID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (inserted.RaidValue - deleted.RaidValue <> 0)
) AS checker
INNER JOIN tss_Rosters ON checker.RaidID = tss_Rosters.RaidID
WHERE (tss_Rosters.Active = 1)
) AS checker2
WHERE (tss_Players.PlayerID = checker2.PlayerID);
/*
For the records where Active changes from True to False,
prevent changes to any other field.
*/
...
/*
Cascade changes to Active.
*/
...
END