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

Last edited Feb 6, 2008 at 10:56 PM by Salaza, version 1

Comments

No comments yet.