There are many occasions where if someone updates a records or even deletes a record we need to keep the Audit trails of the record updated or deleted or even keep a history of records as what are the updates that happened on the database as well as what are the records deleted from the table. In Any database system there are triggers to achieve the same but remember that if triggers if not used efficiently can degrade the performance of the Database system
If we have a table
Code to create table
Code to create the log table
Now We need to write the trigger to create the Audit Trail / log of updation/deletion of record on Details into the logDetails table.
If we have a table
Code to create table
Code:
CREATE TABLE [dbo].[Details] (
[RegNo] [varchar] (50) NOT NULL ,
[RegYear] [varchar] (4) NOT NULL ,
[FullName] [varchar] (50) NOT NULL ,
[FathersName] [varchar] (50) NOT NULL ,
[Location] [varchar] (250) NOT NULL ,
[Village] [varchar] (250) NOT NULL ,
[PresentAdd] [varchar] (500) NOT NULL ,
[CreatedBy] [varchar] (50) NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[UpdatedBy] [varchar] (50) NULL ,
[DateUpdated] [datetime] NULL
)
GO
ALTER TABLE [dbo].[Details] WITH NOCHECK ADD
CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
(
[RegNo],
[RegYear]
) ON [PRIMARY]
GO
Code:
CREATE TABLE [dbo].[logDetails] (
[LogType] [varchar] (50) NULL ,
[RegNo] [varchar] (50) NOT NULL ,
[RegYear] [varchar] (4) NOT NULL ,
[FullName] [varchar] (50) NOT NULL ,
[FathersName] [varchar] (50) NOT NULL ,
[Location] [varchar] (250) NOT NULL ,
[Village] [varchar] (250) NOT NULL ,
[PresentAdd] [varchar] (500) NOT NULL ,
[CreatedBy] [varchar] (50) NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[UpdatedBy] [varchar] (50) NULL ,
[DateUpdated] [datetime] NULL
)
GO
Code:
CREATE TRIGGER AuditDetails
ON dbo.Details
FOR Update,Delete AS
BEGIN
DECLARE @auditType varchar(10)
-- Audit OLD record.
IF EXISTS (Select i.RegNo,i.RegYear from Inserted i)
-- Record is updated
BEGIN
SET @auditType = 'Updated'
END
ELSE
-- Record is deleted
BEGIN
SET @auditType = 'Deleted'
END
INSERT INTO logDetails
(logtype,
RegNo,
RegYear,
FullName,
FathersName,
Location,
Village,
PresentAdd,
CreatedBy,
DateCreated,
UpdatedBy,
DateUpdated
)
SELECT @auditType,
d.RegNo,
d.RegYear,
d.FullName,
d.FathersName,
d.Location,
d.Village,
d.PresentAdd,
d.CreatedBy,
d.DateCreated,
d.UpdatedBy,
d.DateUpdated
FROM deleted d
END
GO
