How to create Audit history table table [closed]

I have a stored procedure which update records. Can anyone help me how to create an audit table that will store previous data and updated data when an update takes place?

I have a stored procedure which updates the table, but I want to keep audit history of previous record and updated record in a separate table after the stored procedure is run. I just want to capture the changes the stored procedure made, in a separate table

Answer

You need to write a trigger function on that specific table.

Check the below example,

CREATE TABLE Customer (Id int, customer_name varchar(50));

INSERT INTO Customer VALUES(1, 'Jhon');
INSERT INTO Customer VALUES(2, 'Smith');

-- Create audit table 
CREATE TABLE [dbo].[AuditTable](
    [Id] [int] NOT NULL,
    [OldValue] [varchar](50) NULL,
    [NewValue] [varchar](50) NULL
) ON [PRIMARY]

Assume you have a customer table and want to insert a audit record whenever a changes made to customer table. Then write a trigger as follow,

CREATE TRIGGER Customer_Audit on Customer
AFTER UPDATE
AS

INSERT INTO AuditTable(Id, OldValue, NewValue) 

select d.Id, d.customer_name, c.customer_name
from deleted d 
inner join Customer c
on d.Id = c.Id 

GO

Now whenever changes made to customer table, a audit record will insert to audit table as follow,

UPDATE Customer SET customer_name = 'Shamique' WHERE id = 2

Output after update the record

Hope that is clear.

Edited

Alter the table with updatedDate and updatedTime column then change trigger query as follow,

INSERT INTO AuditTable(Id, OldValue, NewValue, updateDate, updateTime) 
select d.Id, d.customer_name, c.customer_name, 
CONVERT(VARCHAR(10),getdate(),101) as DatePart, 
CONVERT(VARCHAR(10),getdate(),108) as TimePart from deleted d inner join 
Customer c on d.Id = c.Id

If you need to add Date and Time as one, then simply you can use getDate() syntax in your select statement.