This project is read-only.

Losing DKP through Roster Removal

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.
    */

    ...

    /*
       PlayerID changes, RaidID stays the same.
       Part 1 - Debit old player.
    */

    ...

    /*
       PlayerID changes, RaidID stays the same.
       Part 2 - Credit new player.
    */

    ...

    /*
       PlayerID and RaidID changes.
       Part 1 - Debit old player at old value.
    */

    ...

    /*
       Case of PlayerID and RaidID changes.
       Part 2 - Credit new player at new value.
    */

    ...

    /*
       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.
    */

    UPDATE tss_Players
    SET tss_Players.EarnedDKP = tss_Players.EarnedDKP - checker2.RaidValue,
        tss_Players.SavedDKP = tss_Players.SavedDKP - checker2.RaidValue
    FROM (
        SELECT checker.RaidID,
            checker.PlayerID,
            tss_Raids.RaidValue
        FROM (
            SELECT inserted.RaidID,
                inserted.PlayerID
            FROM inserted
                INNER JOIN deleted ON inserted.RosterID = deleted.RosterID
            WHERE (inserted.Active = 0)
                AND (deleted.Active = 1)) AS checker
            INNER JOIN tss_Raids on checker.RaidID = tss_Raids.RaidID
    ) AS checker2
    WHERE (tss_Players.PlayerID = checker2.PlayerID);

END

Last edited Feb 7, 2008 at 4:32 AM by Salaza, version 2

Comments

No comments yet.