I want to create a bespoke audit process for update functionality in my project. I am thinking something like, I should save the data on the time of page load in the view state and on click of update button save the values again, in this way I will have
the both records before update and after.
I am trying to achieve this but don’t know how I can do this, can anybody suggest that how is it possible.
i would say, go for a stored procedure.....in that first you can select the row of interest, then update the record, then again make a select, you will have final version of row...then you can store it in a audit table with before and after version....
if you use "For Xml auto" in select query, you will get row data in xml format, which you can store in a column in audit table, one column for before and one column for after....
Thanks for your reply mate, in fact my project is quite big, i am trying for some bespoke calss or function. in this way I have to make two extra queries for all of pages ?
You could use trigger to finish what you need. Create a trigger in your SSMS like below.
CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @audit_action varchar(20);
set @audit_action='Orginal Record';
INSERT INTO Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
SELECT *,@audit_action,getdate() FROM DELETED -- insert orginal record
set @audit_action='Updated Record';
INSERT INTO Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
SELECT *,@audit_action,getdate() FROM inserted -- insert updated record
PRINT 'AFTER UPDATE Trigger fired.'
I use sample tables in
Triggers -- Sql Server, you can create the sample table by this article and then test above trigger.
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
Syed Bashara...
Member
256 Points
69 Posts
Audit for Update forms
Nov 06, 2012 10:47 AM|LINK
Hi There,
I want to create a bespoke audit process for update functionality in my project. I am thinking something like, I should save the data on the time of page load in the view state and on click of update button save the values again, in this way I will have the both records before update and after.
I am trying to achieve this but don’t know how I can do this, can anybody suggest that how is it possible.
Many Thanks
ramiramilu
All-Star
95503 Points
14106 Posts
Re: Audit for Update forms
Nov 06, 2012 11:05 AM|LINK
i would say, go for a stored procedure.....in that first you can select the row of interest, then update the record, then again make a select, you will have final version of row...then you can store it in a audit table with before and after version....
if you use "For Xml auto" in select query, you will get row data in xml format, which you can store in a column in audit table, one column for before and one column for after....
Thanks,
JumpStart
Syed Bashara...
Member
256 Points
69 Posts
Re: Audit for Update forms
Nov 06, 2012 11:09 AM|LINK
Hi,
Thanks for your reply mate, in fact my project is quite big, i am trying for some bespoke calss or function. in this way I have to make two extra queries for all of pages ?
Many Thanks
Bash
Chen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: Audit for Update forms
Nov 13, 2012 06:44 AM|LINK
Hi,
You could use trigger to finish what you need. Create a trigger in your SSMS like below.
CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[Employee_Test] FOR UPDATE AS declare @audit_action varchar(20); set @audit_action='Orginal Record'; INSERT INTO Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) SELECT *,@audit_action,getdate() FROM DELETED -- insert orginal record set @audit_action='Updated Record'; INSERT INTO Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) SELECT *,@audit_action,getdate() FROM inserted -- insert updated record PRINT 'AFTER UPDATE Trigger fired.'I use sample tables in Triggers -- Sql Server, you can create the sample table by this article and then test above trigger.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store