Triggering Actions Automatically when Inserting Data

You may need to perform some actions when a new row is to be inserted. Of course, you can make those actions inside a stored procedure, but what if you need to have full control over any insertion or if other applications will insert into your data table in the future? Although you are the developer of your application, your customer is the real owner of the data. In that case, you can manage those changes inside the database by adding an INSERT trigger to your table.

For auditing purposes, suppose that any change in the Product table must be recorded with the date, time, and user . To do this, you add a new ProductHistory table to the database.

 CREATE TABLE [Production].[ProductHistory](    [ProductID] [int] NOT NULL,    [Name] [dbo].[Name] NOT NULL,    [ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [MakeFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlagH] DEFAULT ((1)),    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL CONSTRAINT         [DF_Product_FinishedGoodsFlagH]  DEFAULT ((1)),    [Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [SafetyStockLevel] [smallint] NOT NULL,    [ReorderPoint] [smallint] NOT NULL,    [StandardCost] [money] NOT NULL,    [ListPrice] [money] NOT NULL,    [Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Weight] [decimal](8, 2) NULL,    [DaysToManufacture] [int] NOT NULL,    [ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [ProductSubcategoryID] [int] NULL,    [ProductModelID] [int] NULL,    [SellStartDate] [datetime] NOT NULL,    [SellEndDate] [datetime] NULL,    [DiscontinuedDate] [datetime] NULL,    [rowguid] [uniqueidentifier] ROWGUIDCOL  NULL,    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDateH]         DEFAULT (getdate()),    [Action] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [UserName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL         CONSTRAINT [DF_Product_UserNameH]  DEFAULT (user_name()),  CONSTRAINT [PK_ProductHistory] PRIMARY KEY CLUSTERED (    [ProductID] ASC,    [ModifiedDate] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO USE [AdventureWorks] GO ALTER TABLE [Production].[ProductHistory]  WITH CHECK     ADD CONSTRAINT [CK_ProductHistory] CHECK  (([Action] like '[I,M,D]')) 

Note 

The final part of the script modifies the table by adding a CONSTRAINT to the Action column. The CONSTRAINT admits only three letters for the column, which indicate whether the action was an insertion, modification, or deletion.

After adding the ProductHistory table, you can add a trigger on the Product table to add the row into the ProductHistory table.

 CREATE TRIGGER Production.Product_InsertTrigger    ON Production.Product    AFTER INSERT AS BEGIN    SET NOCOUNT ON; 

 INSERT INTO [AdventureWorks].[Production].[ProductHistory]            ([ProductID],[Name],[ProductNumber],[MakeFlag]            ,[FinishedGoodsFlag],[Color],[SafetyStockLevel]            ,[ReorderPoint],[StandardCost],[ListPrice]            ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode]            ,[Weight],[DaysToManufacture],[ProductLine]            ,[Class],[Style],[ProductSubcategoryID]            ,[ProductModelID],[SellStartDate],[SellEndDate]            ,[DiscontinuedDate],[rowguid],[ModifiedDate]            ,[Action],[UserName])       SELECT [ProductID],[Name],[ProductNumber],[MakeFlag]            ,[FinishedGoodsFlag],[Color],[SafetyStockLevel]            ,[ReorderPoint],[StandardCost],[ListPrice]            ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode]            ,[Weight],[DaysToManufacture],[ProductLine]            ,[Class],[Style],[ProductSubcategoryID]            ,[ProductModelID],[SellStartDate],[SellEndDate]            ,[DiscontinuedDate],[rowguid],GetDate(),'I',USER_NAME()          FROM inserted END 

Note 

This is an AFTER trigger, which executes just after the insertion is completed. An INSTEAD OF trigger is responsible for inserting the appropriate data into the table. In the above scenario, data has already been inserted when the trigger code starts its execution.