February 28, 2014 at 11:58 am
Hi,
I have to create audit log feature and i am aware of creating trigger against the table to get the inserted/updated/deleted data. Writing trigger will be the best option or writing stored procedure inside(insert/update/delete procedure) to insert into audit log? Which will be more efficient?
[Note: My table will have 1 million records]
Any suggestions please
February 28, 2014 at 12:27 pm
The value of the trigger is that it will fire from the stored procedure or say SSMS. So creating a stored procedure to populate the audit may not be the best way. How many columns are you tracking? What is being pushed to the audit table?
February 28, 2014 at 2:04 pm
almost 25 columns i need to track and put it in audit table. any suggestions please
March 1, 2014 at 6:59 am
Can anyone please suggest me
March 1, 2014 at 9:28 am
Using a trigger will allow you to audit changes to the table whether the update/insert/delete occurs from a stored procedure or SSMS.
Have you looked at CDC as an audit solution as well?
March 1, 2014 at 7:33 pm
born2achieve (2/28/2014)
almost 25 columns i need to track and put it in audit table. any suggestions please
Post the CREATE TABLE statement for the audit table, please. I need to see what kind of audit table it is before I make any suggestions.
--Jeff Moden
March 1, 2014 at 9:00 pm
Hi Jeff,
Thanks for your time. My Regular Table and Audit Table has the same schema other than the identity
TableName: Users
IdUser bigint identity(1,1) primary key
FirstName varchar(50)
LastName varchar(50)
Email nvarchar(50)
CreatedDt Datetime
RegisterDate Datetime
IsActive bit
ModifiedDate DateTime
TableName: Users_AuditLog
IdUser_AuditLog bigint identity(1,1) primary key
IdUser bigint
FirstName varchar(50)
LastName varchar(50)
Email nvarchar(50)
CreatedDt Datetime
RegisterDate Datetime
IsActive bit
ModifiedDate DateTime
The IdUser in Users_AuditLog haivng reference key on Users table (IdUser).
Like this i have total columns of 25 in regular table and audit table. The current implementation is on update proc once the table gets updated, as immediately there is an insert statement to audit log table
once update is done here how how the data inserted into audit log table
insert into Users_AuditLog select from users.....
This is how the audit log feature following in my company. So i had different experience with my old company and i have used only triggers for this purpose.
My DBA saying triggers will affect the performance. but i couldn't accept that. That's why i posted this question in this forum. Any suggestion please
March 2, 2014 at 6:09 pm
born2achieve (3/1/2014)
Hi Jeff,Thanks for your time. My Regular Table and Audit Table has the same schema other than the identity
TableName: Users
IdUser bigint identity(1,1) primary key
FirstName varchar(50)
LastName varchar(50)
Email nvarchar(50)
CreatedDt Datetime
RegisterDate Datetime
IsActive bit
ModifiedDate DateTime
TableName: Users_AuditLog
IdUser_AuditLog bigint identity(1,1) primary key
IdUser bigint
FirstName varchar(50)
LastName varchar(50)
Email nvarchar(50)
CreatedDt Datetime
RegisterDate Datetime
IsActive bit
ModifiedDate DateTime
The IdUser in Users_AuditLog haivng reference key on Users table (IdUser).
Like this i have total columns of 25 in regular table and audit table. The current implementation is on update proc once the table gets updated, as immediately there is an insert statement to audit log table
once update is done here how how the data inserted into audit log table
insert into Users_AuditLog select from users.....
This is how the audit log feature following in my company. So i had different experience with my old company and i have used only triggers for this purpose.
My DBA saying triggers will affect the performance. but i couldn't accept that. That's why i posted this question in this forum. Any suggestion please
First of all, this is an audit table and it should take anything that's thrown at it. Because of that, audit tables are a special breed of table where referential integrity not only doesn't matter, but should not even be attempted. Remove any all and FK's that are pointing either from or to the audit table. The only constraint on an audit table should be a primary key on an IDENTITY column that does nothing but provide a unique, narrow, ever increasing ID.
You're DBA is correct. Triggers will affect performance but they won't affect performance badly if they are written correctly. This is yet another valid reason to have absolutely no constraints on the table or its columns other than a PK on the IDENTITY column.
Shifting gears a bit, your audit table is a "whole row" audit table. It's just not necessary to audit INSERTs for such a thing because that immediately requires twice the space for the data and the last insert or update will always be in the original table. With that thought in mind, the only thing that you should be passing data to the audit table should be only from the DELETED trigger table for updates and deletes. That will also make the trigger run a bit faster because it's only going to have to do half the work that everyone expected.
--Jeff Moden
March 2, 2014 at 6:49 pm
Hi Jeff,
Thank you so much for your time and great tip. I agree with your statement. even i also implemented to have only updated/deleted records on the audit table, not all the rows of original table. here they are following different way than what i worked. thought of discussing about it.
Once again thank you so much.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply