Gaining DKP through Loot Removal
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.
*/
...
/*
PlayerID changes, LootValue stays the same.
Part 1 - Credit old player.
*/
...
/*
PlayerID changes, LootValue stays the same.
Part 2 - Debit new player.
*/
...
/*
PlayerID and LootValue change.
Part 1 - Credit old player at old LootValue.
*/
...
/*
PlayerID and LootValue change.
Part 2 - Credit new player at new LootValue.
*/
...
/*
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.
*/
UPDATE tss_Players
SET tss_Players.SpentDKP = tss_Players.SpentDKP - checker.LootValue,
tss_Players.SavedDKP = tss_Players.SavedDKP + checker.LootValue
FROM (
SELECT tss_Loots.LootID,
tss_Loots.PlayerID,
tss_Loots.LootValue
FROM tss_Loots
WHERE tss_Loots.LootID IN (
SELECT inserted.LootID
FROM inserted
INNER JOIN deleted ON inserted.LootID = deleted.LootID
WHERE (inserted.Active = 0)
AND (deleted.Active = 1)
)
) AS checker
WHERE (tss_Players.PlayerID = checker.PlayerID);
END