BEFORE UPDATE trigger in SQL Server

The requirement is to write a copy of the record to a Historical table, as it was prior to the update. This way we will always know what the data looked like and when, by joining the history table to the original.

First, let's create the tables for

• the source data
• and for Historical table where the trigger needs to write the pre-update version of the record

CREATE TABLE [dbo].[additional_charges](

[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[description] [varchar](250) NOT NULL,
[value] [numeric](18, 2) NULL,
[unit] [nvarchar](50) NULL,
[min_value] [numeric](18, 2) NULL,
[min_unit] [nvarchar](50) NULL,
[max_value] [numeric](18, 2) NULL,
[max_unit] [nvarchar](50) NULL,
[effective_from] [date] NULL,
CONSTRAINT [PK_additional_charges] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

CREATE TABLE [dbo].[additional_charges_history](

[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[description] [varchar](250) NOT NULL,
[value] [numeric](18, 2) NULL,
[unit] [nvarchar](50) NULL,
[min_value] [numeric](18, 2) NULL,
[min_unit] [nvarchar](50) NULL,
[max_value] [numeric](18, 2) NULL,
[max_unit] [nvarchar](50) NULL,
[effective_from] [date] NULL,
[effective_to] [date] NULL,
CONSTRAINT [PK_additional_charges_history] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Now we create the trigger that will write the existing version of the record to the hostory table as the update is affected:

SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Akshat Sharma
-- Create date: 31-Oct-2012
-- Description: Write a copy of the record to a historical table
-- =============================================
CREATE TRIGGER [dbo].[CopyInHistory]
ON [dbo].[additional_charges]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF ( UPDATE (value) OR UPDATE (unit) OR UPDATE (min_value) OR UPDATE (min_unit) OR UPDATE (max_value) OR UPDATE (max_unit))
BEGIN
INSERT INTO [dbo].[additional_charges_history]
([code]
,[description]
,[value]
,[unit]
,[min_value]
,[min_unit]
,[max_value]
,[max_unit]
,[effective_from]
,[effective_to])
(SELECT [code]
,[description]
,[value]
,[unit]
,[min_value]
,[min_unit]
,[max_value]
,[max_unit]
,[effective_from]
,CONVERT(VARCHAR(10),GETDATE(),120)
FROM DELETED)
END
END
GO