Discussion Forum

Main >> MS SQL Server and other Databases>> How can you track who edited a field in a table in MS SQL server

All Threads Register Login New Discussion
AuthorPostings
tbrussel
Programmer
How can you track who edited a field in a table in MS SQL server

Wednesday, September 05 2018 10:34 AM

We have a database that is used by many front end web applications. They are in the hundreds but connect using the same connection string to the MS SQL server 2017. Somewhere somehow a data field is being constantly edited by an SQL database query but I cannot track down which program specifically does the update statement. Any ideas?

Post a Replyreply

Jane

Re:How can you track who edited a field in a table in MS SQL server

Wednesday, September 05 2018 10:52 AM

You can write a trigger to track all changes to the database table. It depends how much information you need. For example if you have a table tbl_products and you want to see which app is updating the field product_name, you can create a table that will store the adit data:
-----------------
CREATE TABLE [dbo].[a_audit_trigger](
[id] [int] IDENTITY(1,1) NOT NULL,
[sku] [int] NULL,
[sku_name] [varchar](max) NULL,
[UpdatedBy] [varchar](255) NULL,
[UpdatedOn] [datetime] NULL,
[IP] [varchar](255) NULL,
CONSTRAINT [PK_a_audit_trigger] 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] TEXTIMAGE_ON [PRIMARY]
GO

---------------
Then you will create a trigger to track the changes and see which user login did it and from what location

----------------------------------
CREATE trigger [dbo].[trackAuditRecord] on [dbo].[tbl_products]
after update, insert
as
begin


insert into dbo.a_audit_trigger
(product_id, product_name, UpdatedBy, UpdatedOn,[IP])
select i.product_id, i.product_name, SUSER_SNAME(), getdate(),CONVERT(varchar(50),(select CONNECTIONPROPERTY('client_net_address')))
from tbl_products t
inner join inserted i on t.sku=i.sku
end

Post a Replyreply

AT Integrated Inc. | 909 Ridgebrook Road, Suite 112, Sparks, MD 21152 | T: 410-472-2490
Copyright © 2019 AT Integrated Inc. All rights reserved.