Losing DKP through Roster Removal
CREATE TRIGGER [dbo].[tss_Rosters_UPDATE]
ON [dbo].[tss_Rosters]
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.
*/
...
/*
Updating a Roster entry is complicated and falls into three cases.
The update either changes the RaidID, the PlayerID, or both.
If the RaidID changes, the PlayerID loses the DKP from the deleted
RaidID and gains the DKP from the inserted RaidID.
If the PlayerID changes, the deleted PlayerID loses DKP, and
the new PlayerID gains DKP.
If both are different, the deleted PlayerID loses DKP from
the deleted RaidID, and the new PlayerID receives dKP from the
new RaidID.
*/
/*
RaidID changes, PlayerID stays the same.
*/
...
/*
PlayerID changes, RaidID stays the same.
Part 1 - Debit old player.
*/
...
/*
PlayerID changes, RaidID stays the same.
Part 2 - Credit new player.
*/
...
/*
PlayerID and RaidID changes.
Part 1 - Debit old player at old value.
*/
...
/*
Case of PlayerID and RaidID changes.
Part 2 - Credit new player at new value.
*/
...
/*
For the records where Active changes from True to False,
prevent changes to any other field.
*/
...
/*
For any roster entry marked as inactive, remove that DKP from
the PlayerID.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP - checker2.RaidValue,
tss_Players.SavedDKP = tss_Players.SavedDKP - checker2.RaidValue
FROM (
SELECT checker.RaidID,
checker.PlayerID,
tss_Raids.RaidValue
FROM (
SELECT inserted.RaidID,
inserted.PlayerID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 0)
AND (deleted.Active = 1)) AS checker
INNER JOIN tss_Raids on checker.RaidID = tss_Raids.RaidID
) AS checker2
WHERE (tss_Players.PlayerID = checker2.PlayerID);
END