Gaining/Losing DKP through Roster Modification
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.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP + checker2.fncRaidValueDiff,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker2.fncRaidValueDiff
FROM (
SELECT checker.newPlayerID,
(tss_Raids.RaidValue - tss_Raids2.RaidValue) AS fncRaidValueDiff
FROM (
SELECT inserted.PlayerID AS newPlayerID,
inserted.RaidID AS newRaidID,
deleted.RaidID as oldRaidID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID = inserted.PlayerID)
AND (deleted.RaidID <> inserted.RaidID)
) AS checker
INNER JOIN tss_Raids ON tss_Raids.RaidID = checker.newRaidID
INNER JOIN tss_Raids tss_Raids2 ON tss_Raids2.RaidID = checker.oldRaidID
) AS checker2
WHERE (tss_Players.PlayerID = checker2.newPlayerID);
/*
PlayerID changes, RaidID stays the same.
Part 1 - Debit old player.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP - checker2.RaidValue,
tss_Players.SavedDKP = tss_Players.SavedDKP - checker2.RaidValue
FROM (
SELECT checker.oldPlayerID,
tss_Raids.RaidValue
FROM (
SELECT inserted.RaidID AS newRaidID,
deleted.PlayerID AS oldPlayerID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.RaidID = inserted.RaidID)
) AS checker
INNER JOIN tss_Raids ON tss_Raids.RaidID = checker.newRaidID
) AS checker2
WHERE (tss_Players.PlayerID = checker2.oldPlayerID);
/*
PlayerID changes, RaidID stays the same.
Part 2 - Credit new player.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP + checker2.RaidValue,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker2.RaidValue
FROM (
SELECT checker.newPlayerID,
tss_Raids.RaidValue
FROM (
SELECT inserted.RaidID AS newRaidID,
inserted.PlayerID AS newPlayerID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.RaidID = inserted.RaidID)
) AS checker
INNER JOIN tss_Raids ON tss_Raids.RaidID = checker.newRaidID
) AS checker2
WHERE (tss_Players.PlayerID = checker2.newPlayerID);
/*
PlayerID and RaidID changes.
Part 1 - Debit old player at old value.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP - checker2.RaidValue,
tss_Players.SavedDKP = tss_Players.SavedDKP - checker2.RaidValue
FROM (
SELECT checker.oldPlayerID,
tss_Raids.RaidValue
FROM (
SELECT deleted.RaidID AS oldRaidID,
deleted.PlayerID AS oldPlayerID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.RaidID <> inserted.RaidID)
) AS checker
INNER JOIN tss_Raids ON tss_Raids.RaidID = checker.oldRaidID
) AS checker2
WHERE (tss_Players.PlayerId = checker2.oldPlayerID);
/*
Case of PlayerID and RaidID changes.
Part 2 - Credit new player at new value.
*/
UPDATE tss_Players
SET tss_Players.EarnedDKP = tss_Players.EarnedDKP + checker2.RaidValue,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker2.RaidValue
FROM (
SELECT checker.newPlayerID,
tss_Raids.RaidValue
FROM (
SELECT inserted.RaidID AS newRaidID,
inserted.PlayerID AS newPlayerID
FROM inserted
INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.RaidID <> inserted.RaidID)
) AS checker
INNER JOIN tss_Raids ON tss_Raids.RaidID = checker.newRaidID
) AS checker2
WHERE (tss_Players.PlayerID = checker2.newPlayerID);
/*
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.
*/
...
END