Track data change in a table.

  • I have created a simple trigger that fires when a change is made to my work_order table. However, I'm having problems bringing other information into the script. For instance, I would like to see the client_net_address, and what data actually changed (oldvalue - newvalue). Can anyone point me in the right direction? Any help will be greatly appreciated.

    Here is my current trigger:

    CREATE TRIGGER WO_AUDIT

    ON WORK_ORDER

    FOR INSERT, UPDATE, DELETE AS

    BEGIN

    INSERT INTO ENV_AUDIT (BASE_ID, MODIFIED_DATE, USER_ID)

    SELECT BASE_ID, GETDATE(), SUSER_SNAME()

    FROM INSERTED

    END

  • I am providing you a sample Trigger that i guess will help you

    CREATE TRIGGER [dbo].[utr_AUDIT_UPDATE_tablename]

    ON [dbo].[tablename]

    AFTER INSERT,UPDATE AS

    DECLARE @user-id varchar(20),

    @AppID varchar(50),

    @OldValue varchar(50),

    @NewValue varchar(50),

    @FieldName varchar(50)

    -- Get UserID and ApplicationID.

    SELECT @user-id = RTRIM(sp.loginame),

    @AppID = RTRIM(sp.program_name)

    FROM Master..sysprocesses sp

    WHERE spid=@@spid

    -- Trace Updates to column.

    IF UPDATE(columnname)

    BEGIN

    -- Set field name being captured.

    SELECT @FieldName = 'columnname'

    -- Get Old value.

    SELECT @OldValue = columnname

    FROM deleted

    -- Get New value.

    SELECT @NewValue = columnname

    FROM inserted

    -- Verify that there was a change.

    IF ((ISNULL(@OldValue, 'Y') = 'Y') AND (ISNULL(@NewValue, 'Y') <> 'Y'))

    OR ((ISNULL(@OldValue, 'Y') <> 'Y') AND (ISNULL(@NewValue, 'Y') = 'Y'))

    OR (@OldValue <> @NewValue)

    BEGIN

    -- Save change.(all the change information is collected in AUDIT_Table)

    INSERT INTO AUDIT_Table

    (DatabaseName,

    TableName,

    FieldName,

    OldValue,

    NewValue,

    UserID,

    AppID)

    Values(db_name(),

    'tablename',

    @FieldName,

    @OldValue,

    @NewValue,

    @user-id,

    @AppID)

    END

    END

    Similary for all coulmns you need to audit ,above procedure Repeats.

    Good Luck

  • Do I need to pick a specific "columnname" for it to look at for changes?

    Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 17

    Invalid column name 'columnname'.

    Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 23

    Invalid column name 'columnname'.

    Msg 207, Level 16, State 1, Procedure utr_AUDIT_UPDATE_WORK_ORDER, Line 27

    Invalid column name 'columnname'.

  • Got it. Thanks.

  • These two articles have a lot of information on logging and auditing:

    http://www.sqlservercentral.com/articles/Auditing/63247/

    http://www.sqlservercentral.com/articles/Auditing/63248/

    Their discussions add a lot to them.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Nary, your example trigger is very flawed in that it doesn't handle multi-record actions.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I am just learning this stuff, but it seemed to do what we wanted. I did go back in and add the work order id and getdate. Any further help on improving this trigger would be greatly appreciated. I am a work in progress, but from what I here this is the place to learn it.

  • Take a look at the sample triggers in the articles and discussions I linked. Those are tested and proven.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • You may want to version the table (see the link in my sig to get a document explaining the details). But that allows you to query the table to see the data as it existed at different times in the past. That might be more than what you need. If all you want to do is track changes and generate some before/after reports, just create a replica table with a timestamp column which will be part of the key.

    -- Original table

    create table MainTable(

    PKcol ...,

    Col1 ...,

    Col2 ...,

    Col3 ...,

    constraint PK_MainTable primary key (PKcol)

    );

    create table TrackingTable(

    PKcol ..., -- if this is Identity in MainTable, do NOT make it so here!

    EffDate datetime not null,

    Op char(1),

    Col1 ...,

    Col2 ...,

    Col3 ...,

    constraint PK_TrackingTable primary key (PKcol, EffDate)

    );

    Then the trigger on the main table table would just be this:create trigger T_Name on MainTable for Insert, Update, Delete

    as begin

    declare @Icount int, @Dcount int;

    select @Icount = count(*) from Inserted;

    select @Dcount = count(*) from Deleted;

    if @Icount + @Dcount = 0

    return;

    if @Dcount = 0 begin

    -- Insert operation

    Insert TrackingTable(PKcol, EffDate, OP, ...)

    select PKcol, GetDate(), 'I', ...

    from Inserted;

    end;

    else if @ICount = 0 begin

    -- Delete operation

    Insert TrackingTable(PKcol, EffDate, OP)

    select PKcol, GetDate(), 'D'

    from Deleted;

    end;

    else begin

    -- Update operation

    Insert TrackingTable(PKcol, EffDate, OP, ...)

    select PKcol, GetDate(), 'U', ...

    from Inserted;

    end;

    end;

    That way, your tracking table has a complete record of when an entity was created, every change made to it, and when it was deleted. You may, of course, add user info and other information you also want to track and omit the columns (if any) you don't need to track. This is a simplified versioning scheme but I think it gives you what you want.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • You may try this one out.

    Here I used table (POS_Products) and table (Audit)

    It may work out good for you as it caters for all columns changed;

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER trigger [dbo].[tr_TriggerTest] on [dbo].[POS_Products] for insert, update, delete

    as

    declare

    @Bit int,

    @Field int,

    @maxfield int,

    @Char int,

    @FieldName varchar(128),

    @TableName varchar(128),

    @PKCols varchar(100),

    @sql varchar(2000),

    @UpdateDate varchar(21),

    @UserName varchar(128),

    @Type varchar(1),

    @PKSelect varchar(100)

    --Specify the name of the table to be audited

    select @TableName = 'POS_Products'

    --Gets the name of the user making changes to the table

    select @UserName = system_user, @UpdateDate = convert(varchar(8),getdate(),112)

    + ' ' + convert(varchar(12), getdate(),114)

    --Action

    if exists(select * from inserted)

    if exists(select * from deleted)

    select @Type = 'U'

    else

    select @Type = 'I'

    else

    select @Type = 'D'

    --Gets list of columns

    select * into #ins from inserted

    select * into #del from deleted

    --Gets primary key columns for full outer join

    select @PKCols = coalesce(@PKCols + ' and', ' on')

    + ' i.'+ c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

    from information_schema.table_constraints pk, information_schema.key_column_usage c

    where pk.table_name = @TableName

    and constraint_type = 'PRIMARY KEY'

    and c.table_name = pk.table_name

    and c.constraint_name = pk.constraint_name

    --Get Primary key for Insert

    select @PKSelect = coalesce(@PKSelect + '+', '')

    + '''<' + column_name

    + '=''+ convert(varchar(100),

    coalesce(i.'+ column_name +',d.' + column_name +')) + ''>'''

    from information_schema.table_constraints pk,

    information_schema.key_column_usage c

    where pk.table_name = @TableName

    and constraint_type = 'primary key'

    and c.table_name = pk.table_name

    and c.constraint_name = pk.constraint_name

    if @PKCols is null

    begin

    raiserror('no PK on table %s', 16, -1, @TableName)

    return

    end

    select @field = 0,

    @maxfield = max(Ordinal_Position)

    from information_schema.columns

    where table_name = @TableName

    while @field = @maxfield

    begin

    select @field = min(Ordinal_Position)

    from information_schema.columns

    where table_name = @TableName

    and ordinal_position > @field

    select @bit = (@field - 1)% 8 + 1

    select @bit = power(2,@bit - 1)

    select @char = ((@field - 1)/8)+1

    if substring(columns_updated(), @char,1) & @bit > 0 or @Type in('I', 'D')

    begin

    select @FieldName = column_name

    from information_schema.columns

    where table_name = @TableName

    and Ordinal_Position = @field

    select @sql = 'insert into Audit (Type, TableName, PK, FieldName, OldValue, NewValue,

    UpdateDate, UserName)

    values (select ''' + @Type +''','''

    + @TableName +''','+ @PKSelect

    + ','''+ @FieldName + ''''

    + ', convert(varchar(1000), d.' + @FieldName + ')'

    + ', convert(varchar(1000), i.' + @FieldName + ')'

    + ','''+ @UpdateDate + ''''

    + ','''+ @UserName + ''''

    + ' from #ins i full outer join #del d'

    + @PKCols

    + ' where i.' + @fieldname + '<> d.' + @fieldname

    + ' or (i.' + @fieldname + ' is null and d.'

    + @fieldname

    + ' is not null)'

    + ' or (i.' + @fieldname + ' is not null and d.'

    + @fieldname

    + ' is null))'

    exec(@sql)

    end

    end

  • Thanks for all the help. It is greatly appreciated.

  • When I run this it adds the trigger, but once I go to change the address I get this error

    A save operation on table 'RM_Customer_MSTR cannot find the table trying to add a trigger

    What I'm wondering is if this is related to the INSERT INTO AUDIT_Table

    Should this table already be there or does the code add it?

  • I created this trigger and it populates the table with the correct data but it will not run the email portion at the bottom. Can someone tell me what i am doing wrong.

    CREATE TRIGGER [dbo].[AUDIT_UPDATE_RM00101]

    ON [dbo].[RM00101]

    AFTER INSERT,UPDATE,DELETE AS

    DECLARE @user-id varchar(20),

    @getdate-2 varchar(39),

    @OldValue varchar(50),

    @NewValue varchar(50),

    @FieldName varchar(50),

    @sql varchar(1000),

    @DBNAMEvarchar(10)

    -- Get UserID and ApplicationID.

    SELECT @user-id = RTRIM(sp.loginame),

    @getdate-2 = RTRIM(getdate())

    FROM Master..sysprocesses sp

    WHERE spid=@@spid

    -- Trace Updates to column.

    IF UPDATE(address1)

    BEGIN

    -- Set field name being captured.

    SELECT @FieldName = 'Address1'

    -- Get Old value.

    SELECT @OldValue = address1

    FROM deleted

    -- Get New value.

    SELECT @NewValue = address1

    FROM inserted

    -- Verify that there was a change.

    IF ((ISNULL(@OldValue, 'Y') = 'Y') AND (ISNULL(@NewValue, 'Y') <> 'Y'))

    OR ((ISNULL(@OldValue, 'Y') <> 'Y') AND (ISNULL(@NewValue, 'Y') = 'Y'))

    OR (@OldValue <> @NewValue)

    BEGIN

    -- Save change.(all the change information is collected in AUDIT_Table)

    /*CREATE TABLE AUDIT_Table

    (Date_Changed varchar(39), FieldName VARCHAR(50), OldValue VARCHAR(50), NewValue VARCHAR(50),

    UserID VARCHAR(20));*/

    INSERT INTO AUDIT_Table

    --TableName,

    (FieldName,

    OldValue,

    NewValue,

    UserID,

    Date_Changed)

    Values

    (

    @FieldName,

    @OldValue,

    @NewValue,

    @user-id,

    @getdate-2 --+ convert(varchar, getdate(), 10)

    )

    SET@DBNAME= 'Test5' --set this variable as the name of your database

    SET@sql= 'SELECT Date_Changed, FieldName, OldValue, UserID

    FROM ' + @DBNAME + '.dbo.AUDIT_Table WHERE Date_Changed >= ' + char(39) +

    convert(varchar(21), GetDate(), 101) + char(39)

    EXEC master.dbo.xp_sendmail

    @recipients = 'lcarrethers@dolese.com',

    -- @copy_recipients = @COPY_RECIPIENTS,

    -- @blind_copy_recipients = @BLIND_COPY_RECIPIENTS,

    @subject = 'Address changed',

    @message = "Attached is a list of address's that have been changed today",

    @query = @sql,

    @attach_results = 'TRUE',

    @width = 250

    END

    END

    GO

  • what error do you get? Have you checked various logs?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Oh, one more thing - be wary of using xp's in triggers. Better would be to put relevant data into a table that is asynchronously used to send the email if your process can allow for that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply