Data Change Tracing

  • Hello,

    I am a very db knowledgeable developer but not a db developer so forgive me for the simple question. Our web app is changing data and we can't seem to identify why.

    This only happens to a few people and we have not yet figured out how to reproduce in test...thus finding and fixing is very difficult.

    Quickly what happens is the user logs in and changes some features. In the database we change these values to 0 or 1 in a bit field. The user logs off, logs back in and as is well. Indeed it seems that it only happens after a long period of time. So the user will stay logged out for 4 hours, log back in and the settings have changed???

    We are unsure if this happens on it's own or during login but only after a "cooling off period". So I was looking for something that I could configure to watch table X and better yet watch rows 1-5 in table X. From there I would then be able to identify a job, or a stored procedure etc. to start figuring this out.

    Thank You

    JB

  • If you have access to BOL (Books On Line - the SQL server help file) you may want to investigate the subject "SQL Server Audit Action Groups and Actions ". It seems to be an applicable method of tracing those changes you have described.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • BOL??? Who needs that when you have Google? Thank You for pointing me in the right direction. Well so much for that idea. Only available SQL 2008 and this is 2005.

    JB

  • Hi,

    You could setup a trigger on the table to get details of when the row was updated and store the details in an audit table

    There are many different approaches to doing this, but if you need some help, let me know and i'll post some code which is easy to implement.

    if your application is on a domain, then the trigger will be able to pickup which user made the changes - if it's not on a domain, then its likely that the application will be running under a general account - something like IIS_USER on windows I think. Like you say - it could also be something in the application that is changing this - if you have a trigger on the table, at least you can see exactly when it's happening which might give you a better clue.

  • Well I've written triggers before but not for auditing. Typically just for business rules purposes. So my question is would I have a parameter available telling me stored procedure XYZ changed it or maintenance job XYZ etc. I know it is getting changed I can see that in that SSMS what I need is a clue who. If you have code to help that would be helpful or even just some trigger parms / values to read up on.

    TIA

    JB

  • Hi,

    I'll give you sthe stuff I use - this will tell you at least who and when the data was updated.

    Step 1 create the table to store the updates.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Tbl_UpdateMonitor](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [TableName] [varchar](255) NULL,

    [ChangeDate] [datetime] NULL,

    [OldValues] [xml] NULL,

    [newValues] [xml] NULL,

    [Username] [varchar](50) NULL,

    CONSTRAINT [PK_Tbl_UpdateMonitor] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[Tbl_UpdateMonitor] ADD CONSTRAINT [DF_Tbl_UpdateMonitor_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate]

    GO

    Step 2 Create a stored procedure to inset details of the updates into the audit table.

    CREATE PROCEDURE [dbo].[u_proc_UpdateMonitor]

    @TableName sysname,

    @Deleted xml,

    @Inserted XML

    AS

    BEGIN

    if @Deleted is not null or @Inserted is not NULL

    BEGIN

    insert into Tbl_UpdateMonitor (TableName, OldValues, NewValues, UserName)

    values (@TableName, @Deleted, @Inserted, SYSTEM_USER)

    END

    END

    (make sure that public has execute on this)

    Step 3 Create the trigger on the table - just replace TABLENAME with the name of the table you want to monitor.

    CREATE trigger [dbo].[Tbl_TABLENAME_T_Log] on [dbo].[Tbl_TABLENAME]

    for insert, update, delete

    as

    declare @i xml, @d xml, @t VARCHAR(255)

    SET @T = (select object_name(id) from sysobjects where instrig = @@procid)

    set @i = (SELECT * from inserted for xml auto)

    set @d = (select * from deleted for xml auto)

    exec u_proc_UpdateMonitor @T,@D, @I

    GO

    Hope this is of some help - Good luck!

  • craigB-921010,

    I don't see where on this forum I can mark as closed nor give credit other than another post. It took awhile to roll through QA and prod cycles. The script didn't point a finger (not sure it could have) but it did provide historical data and I was able to notice a pattern in the data change. From there I narrowed it down to one stored procedure and then a couple lines of code and not it is fixed.

    Thank You Very Much!

    JB

  • glad to help. 🙂

    In case you need to reference this in future, I have done some improvements to the update trigger recently. This means the updates are stored as propper structured XML - the benefit of doing this, is you can use it as a datasource in an application for viewing the change log - I have also written that too - it loads the changes whiich you can filter by user, table, date etc, and then hightlights which fields have been modified. if you think it'd be useful, give me a shout

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER trigger [dbo].[Tbl_TABLENAME_T_Log] on [dbo].[Tbl_TABLENAME]

    for insert, update, delete

    as

    declare @i xml, @d xml, @t VARCHAR(255)

    SET @T = (select object_name(id) from sysobjects where instrig = @@procid)--this is the current table

    SET @i = (SELECT * FROM inserted for xml PATH('fields'), ROOT('newvalues'))

    SET @d = (SELECT * FROM deleted for xml PATH('fields'), ROOT('oldvalues'))

    exec u_proc_UpdateMonitor @T,@D, @I

Viewing 8 posts - 1 through 7 (of 7 total)

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