Gaining/Losing DKP through Loot Modification
CREATE TRIGGER [dbo].[tss_Loots_UPDATE]
ON [dbo].[tss_Loots]
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 Loot entry is slightly less complicated than a Roster entry.
If the PlayerID changes, the old PlayerID regains the DKP spent,
and the new PlayerID loses the DKP spent.
If the LootValue changes and the PlayerID doesn't change, the
PlayerID is credit/debit the difference.
If the PlayerID changes and the LootValue changes, the old
ID regains the DKP original spent and the new PlayerID is debit
the new DKP spent.
*/
/*
LootValue changes, PlayerID stays the same.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.EarnedDKP + checker.fncLootValueDiff,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker.fncLootValueDiff
FROM (
SELECT (inserted.LootValue - deleted.LootValue) AS fncLootValueDiff,
inserted.PlayerID
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (inserted.LootValue - deleted.LootValue <> 0)
AND (inserted.PlayerID = deleted.PlayerID)
) AS checker
WHERE (tss_Players.PlayerID = checker.PlayerID);
/*
PlayerID changes, LootValue stays the same.
Part 1 - Credit old player.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.SpentDKP - checker.LootValue,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker.LootValue
FROM (
SELECT deleted.PlayerID AS oldPlayerID,
inserted.LootValue
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.LootValue = inserted.LootValue)
) AS checker
WHERE (tss_Players.PlayerID = checker.oldPlayerID);
/*
PlayerID changes, LootValue stays the same.
Part 2 - Debit new player.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.SpentDKP + checker.LootValue,
tss_Players.SavedDKP = tss_Players.SavedDKP - checker.LootValue
FROM (
SELECT inserted.PlayerID AS newPlayerID,
inserted.LootValue
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.LootValue = inserted.LootValue)
) AS checker
WHERE (tss_Players.PlayerID = checker.newPlayerID);
/*
PlayerID and LootValue change.
Part 1 - Credit old player at old LootValue.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.SpentDKP - checker.LootValue,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker.LootValue
FROM (
SELECT deleted.PlayerID AS oldPlayerID,
deleted.LootValue
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.LootValue <> inserted.LootValue)
) AS checker
WHERE (tss_Players.PlayerID = checker.oldPlayerID);
/*
PlayerID and LootValue change.
Part 2 - Credit new player at new LootValue.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.SpentDKP + checker.LootValue,
tss_Players.SavedDKP = tss_Players.SavedDKP - checker.LootValue
FROM (
SELECT inserted.PlayerID AS newPlayerID,
inserted.LootValue
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 1)
AND (deleted.Active = 1)
AND (deleted.PlayerID <> inserted.PlayerID)
AND (deleted.LootValue <> inserted.LootValue)
) AS checker
WHERE (tss_Players.PlayerID = checker.newPlayerID);
/*
For the records where Active changes from True to False,
prevent changes to any other field.
*/
...
/*
For any loot entry marked as inactive, remove that DKP from
the PlayerID.
*/
...
END