SQL server INSTEAD OF TRIGGER

First of all, I am not a fan of triggers because of a number of reasons, but i will not go into those in this blog. Anyway, triggers can be useful sometimes, and there are two types of triggers, AFTER trigger and INSTEAD OF TRIGGER. In this blog, I’ll focus on the INSTEAD OF TRIGGER.

When this trigger is fired it will handle all records in the same call. That means that you need to take care of all the records in the INSERTED or UPDATED table and update your undlerlying table. Take a look at this sample:

SET ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM [INFORMATION_SCHEMA].TABLES t WHERE t.[TABLE_SCHEMA] ='dbo' AND t.[TABLE_NAME] = 'triggerTest')
	DROP TABLE [dbo].[triggerTest]
GO
	 
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[triggerTest](
	[col1] [int] IDENTITY(1,1) NOT NULL,
	[myName] [varchar](50) COLLATE Finnish_Swedish_CI_AS NOT NULL,
	lastUpdated DATE NULL
 CONSTRAINT [PK_triggerTest] PRIMARY KEY CLUSTERED 
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.trigg 
   ON  dbo.triggerTest 
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	
	-- get the records from inserted table
 	DECLARE @name VARCHAR(50)
	SELECT @name =	[myName] FROM INSERTED

	IF EXISTS(SELECT [myName] FROM [dbo].[triggerTest] WHERE [myName] = @name)
		UPDATE [dbo].[triggerTest]
		SET lastUpdated = GETDATE()
		WHERE [dbo].[triggerTest].[myName] = @name 
	ELSE
		-- insert the records in the table
		INSERT INTO dbo.[triggerTest] ([myName])
		VALUES (@name)   
END
GO


-- insert three records in triggerTest
INSERT INTO [dbo].[triggerTest] 
	([myName])
VALUES
	('Håkan Winther'),
	('Jan Nieminen'),
	('Göran Rönnbäck')

-- and take a look at the result
SELECT * FROM dbo.[triggerTest] AS tt 

--as you can see, only one record is inserted
--that's because the instead of trigger is 
--handling all records in the same call
--and my trigger is only handling one record

GO

ALTER TRIGGER dbo.trigg 
   ON  dbo.triggerTest 
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	
	-- update existing records
	UPDATE [dbo].[triggerTest]
	SET lastUpdated = GETDATE()
	WHERE [dbo].[triggerTest].[myName] IN (SELECT [myName] FROM INSERTED)

	-- insert the new records in the table
	INSERT INTO dbo.[triggerTest] ([myName])
	SELECT i.[myName] FROM INSERTED I 
	LEFT JOIN [dbo].[triggerTest] AS TT 
	ON I.myName = tt.[myName]
	WHERE tt.col1 IS NULL
END
GO

-- insert three records in triggerTest
INSERT INTO [dbo].[triggerTest] 
	([myName])
VALUES
	('Håkan Winther'),
	('Jan Nieminen'),
	('Göran Rönnbäck')

-- and take a look at the result
SELECT * FROM dbo.[triggerTest] AS tt 

As you can see, before we changed the trigger, only the last record is inserted (or updated). After the trigger was changed to use the INSERTED table, the complete set of records was inserted or updated. Make sure you take care of all records in the trigger, otherwise you are in serious trouble.

If you have any SQL server related questions, feel free to contact any of our SQL server consultants and we will help you.

Håkan Winther
twitter: @h_winther