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
 reply
|
