Combined Trigger for Insert, Update, and Delete in SQL

Software : SQL

Language : SQL

Goal: To have 1 trigger for insert, update, and delete operation in SQL.

Steps:

• Create 2 tables. Let's name the first table "Component" and the second table "ComponentHist". Every insertion, modification, or deletion of the 'Component' data will be tracked in 'ComponentHist' table. An action will be used to indicate the operation done to the 'Component' table, which will have one of the following value:
• 'I' (insert)
• 'U' (Update)
• 'D' (Delete).

CREATE TABLE [dbo].[Component](

 [ComponentId] int IDENTITY(1,1) NOT NULL,
[ComponentType] varchar(3) NOT NULL,

 [ComponentName] nvarchar(100) NOT NULL,
[ComponentCode] varchar(20) NOT NULL,

 CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED 
[ComponentConfig] nvarchar(200) NULL,
 [Description] nvarchar(500) NULL,
(

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[ComponentId] ASC
) ON [PRIMARY]

CREATE TABLE [dbo].[ComponentHist](

 [ComponentCode] varchar(20) NOT NULL,
[ComponentId] int NOT NULL,
 [ComponentType] varchar(3) NOT NULL,

 [Description] nvarchar(500) NULL,
[ComponentName] nvarchar(100) NOT NULL,
 [ComponentConfig] nvarchar(200) NULL,
 [Action] char(1) NOT NULL,

 [ComponentId] ASC,
[CreatedDate] datetime NOT NULL,
 CONSTRAINT [PK_ComponentHist] PRIMARY KEY CLUSTERED 
(
 [CreatedDate] ASC

) ON [PRIMARY]

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

• Create a trigger

CREATE TRIGGER [dbo].[trg_Component]
ON [dbo].[Component]
WITH ENCRYPTION

BEGIN
AFTER INSERT, UPDATE, DELETE
AS 
 DECLARE @Action char(1)

   WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) THEN 'U'
SET @Action = (
  CASE
   WHEN EXISTS (SELECT * FROM inserted) THEN 'I' 

 INSERT INTO [ComponentHist]
WHEN EXISTS (SELECT * FROM deleted) THEN 'D'
   ELSE NULL 
  END
 )

 SELECT *, @Action, getdate()
 FROM inserted

END