September 21, 2017 at 7:40 am
Our team came up with the idea to add Audit columns in all the tables in all DBs we have so we can keep track of who did what, when etc.create table Test (
column 1 int IDENTITY (1,1) PRIMARY KEY,
column 2 varchar(50),
column 3 varchar(50),
Created_By VARCHAR (30) CONSTRAINT UserLogInfo_CreatedBy DEFAULT(SUSER_NAME()), --created a constraint on created by column and use the suser function to populate with currently logged in user
Date_Created DATETIME NOT NULL DEFAULT GETDATE()) --This will generate a system date when the new entry is made
This is the trigger which I am supposed to create for all the tables in the DB. This is going to be a standard we are going to follow.create trigger trg_Update
on TableName
after update
as
set nocount on
update TableName
set Updated_By = SUSER_SNAME(),
Modified_Date = GetDate()
Question: By adding these 2 audit columns, does it makes sense to have it in all the tables moving forward? I am also talking about having triggers so how many triggers are too many? Does this approach make sense? Is there a better idea?
September 21, 2017 at 8:16 am
Why not keep it in a separate table? If you have it in the same table, you're going to get page splits the first time any row is updated. You also need to read all that audit information into memory to satisfy every query against the table, which is a waste of IO and RAM.
John
September 21, 2017 at 8:44 am
I would try to avoid doing this in triggers. You could add the new data points in your update queries (hopefully you are using stored procs so this is a lot easier).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply