ALTER TRIGGER [dbo].[trg_tlkpSegmentGroupSource_AuditUpdates]
ON [dbo].[tlkpSegmentGroupSource]
--Author:    John Steinbeck
--Date:      24 Sept 08
--Purpose:   This Trigger is designed to capture any column whose value is updated... you will specify what columns you want
FOR UPDATE
AS

    ---- Declarations
    DECLARE @TableName  VARCHAR(100)
    DECLARE @KeyField   VARCHAR(100)
    DECLARE @KeyVal     VARCHAR(100)
    DECLARE @OldVal     VARCHAR(500)
    DECLARE @NewVal     VARCHAR(500)
    DECLARE @COL_NAME   NVARCHAR(100)
    DECLARE @SYSUSER    VARCHAR(100)
    DECLARE @GETDATE    DATETIME
    DECLARE @XID        VARCHAR(50)

    ---- Instantiate
    SET @TableName = 'tlkpSegmentGroupSource'                                --Table Name of the Table used for this Trigger CHANGE MY VALUE
    SET @KeyField = 'SegmentGroupSourceID'    
SELECT @KeyVal = SegmentGroupSourceID FROM INSERTED                        --CHANGE MY VALUE
    SET @GETDATE = CAST(GETDATE() AS DATETIME)
    SET @XID= SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER)+1,(LEN(SYSTEM_USER)+1) - CHARINDEX('\',SYSTEM_USER))
    SELECT @SYSUSER = USERNAME FROM tblAuthorizations
    WHERE XID = @XID

    SET @SYSUSER = ISNULL(@SYSUSER, @XID) 
    -- FOR INSERT AND UPDATE ONLY...
    ---- Update Audit Fields on Table
    Update tlkpSegmentGroupSource
    SET UpdatedBy = @SYSUSER, LastUpdate = @GETDATE 
    WHERE SegmentGroupSourceID IN (SELECT SegmentGroupSourceID FROM Inserted)

    -- LOOPING THRU SYSTEM FUNCTION TO SEE WHAT COLUMNS HAVE BEEN UPDATED AND GET THE NAMES... STORE IN TEMP TBL
    INSERT INTO tblAuditLog
        (TableName, KeyField, KeyValue, UpdatedBy, LastUpdate, ChangedColumn, OldValue, NewValue)
    SELECT
        @TableName          AS TBL
        , @KeyField         AS KY
        , @KeyVal           AS KV
        , @SYSUSER          AS SYS
        , @GETDATE          AS THEDATE
        , OLD.NAME          AS COLNAME
        , ISNULL(CAST(OLD.OLD_COL_VALUES AS VARCHAR(500)),'')   AS OLD
        , ISNULL(CAST(NEW.NEW_COL_VALUES AS VARCHAR(500)),'')   AS NEW
    FROM   (
            SELECT
                COLS.N
                , (CASE COLS.N
                    WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
                    WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
                    WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
                    WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
                    WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
                  END) OLD_COL_VALUES
            , COLS.NAME
            FROM
                (
                SELECT DISTINCT *
                FROM DELETED
                 [Wink]  D
                CROSS Join
                (
                SELECT
                    S.NAME, T.N AS N
                FROM
                    DBO.TALLY T INNER JOIN SYSCOLUMNS S
                    ON T.N = S.COLID
                WHERE ((N IN (2, 3, 4, 5, 6))         -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
                AND ID = object_id(@TableName))
                AND (CASE
                        WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
                        THEN 1
                        ELSE 0
                        End
                     [Wink]  = 1
                 [Wink]  COLS
             [Wink]  OLD INNER JOIN
            (
            SELECT
                COLS.N
                , (CASE COLS.N
                    WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
                    WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
                    WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
                    WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
                    WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
                  END) NEW_COL_VALUES
            , COLS.NAME
            FROM
                (
                SELECT DISTINCT *
                FROM INSERTED
                 [Wink]  I 
                CROSS Join
                (
                SELECT
                    S.NAME, T.N AS N
                FROM
                    DBO.TALLY T INNER JOIN SYSCOLUMNS S
                    ON T.N = S.COLID
                WHERE ((N IN (2, 3, 4, 5, 6))         -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
                AND ID = object_id(@TableName))
                AND (CASE
                        WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
                        THEN 1
                        ELSE 0
                        End
                     [Wink]  = 1
                 [Wink]  COLS
             [Wink]  NEW
            ON OLD.N = NEW.N