This project is read-only.

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

Last edited Feb 7, 2008 at 12:01 AM by Salaza, version 1

Comments

No comments yet.