This project is read-only.

Gaining/Losing DKP through Raid Value Changes

CREATE TRIGGER [dbo].[tss_Raids_UPDATE] 
   ON [dbo].[tss_Raids]
   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.
    */

    ...

    /*
       If the RaidValue has changed, update the DKP for all Players
       that attended that event. This is for Raids not marked as inactive.
    */

    UPDATE tss_Players
    SET tss_Players.EarnedDKP = tss_Players.EarnedDKP + checker2.fncRaidValueDiff,
        tss_Players.SavedDKP = tss_Players.SavedDKP + checker2.fncRaidValueDiff
    FROM (
        SELECT checker.fncRaidValueDiff,
            tss_Rosters.PlayerID
        FROM (
            SELECT (inserted.RaidValue - deleted.RaidValue) AS fncRaidValueDiff,
                inserted.RaidID
            FROM inserted
                INNER JOIN deleted ON inserted.RaidID = deleted.RaidID
            WHERE (inserted.Active = 1)
                AND (deleted.Active = 1)
                AND (inserted.RaidValue - deleted.RaidValue <> 0)
        ) AS checker
            INNER JOIN tss_Rosters ON checker.RaidID = tss_Rosters.RaidID
        WHERE (tss_Rosters.Active = 1)
    ) AS checker2
    WHERE (tss_Players.PlayerID = checker2.PlayerID);

    /*
       For the records where Active changes from True to False,
       prevent changes to any other field.
    */

    ...

    /*
       Cascade changes to Active.
    */

    ...

END

Last edited Feb 7, 2008 at 5:38 AM by Salaza, version 2

Comments

No comments yet.