INSTEAD Of INSERT Triggers in SQL Server

The SQL Server Instead Of INSERT Trigger will fire before the Insert operation on a table starts. In this article, we will show you, How to write the INSTEAD OF INSERT Triggers in SQL Server with an example.

For this demonstration, We are going to use the below-shown tables. As you can see from the below screenshot that our Employee table is Empty. Here, our task is to create INSTEAD OF INSERT TRIGGER on this Employee table. And by using this trigger, we want to restrict the records inserting into Employee Table, and also insert records into the Audit table, along with the audit information

TIP: You can refer INSTEAD OF DELETE article to understand the INSTEAD OF INSERT Trigger, and INSTEAD OF UPDATE article for INSTEAD OF UPDATE Trigger.

and our Employee Table Audit is also empty

NOTE: SQL Instead of Insert Triggers can be created in Tables and Views. In general, we use them on View

Instead Of INSERT Triggers in SQL Server Example

In this example, we will show you, How to create an Instead of insert Triggers in SQL Server. Next, we will create a trigger on the Employee table using the CREATE TRIGGER Statement.

Remember, this trigger will fire before the Insert operation on Employee table starts. Once it completes inserting into Employee Audit table, it will start inserting into the Employee table. And if it fails for some reason then it won’t insert into the Employee table.

From the below code snippet you can see that, we are using the INSERT INTO SELECT Statement to select all the records that are inserting into the Employee table, and then

• We are inserting the records whose yearly Income is less than or equal to 70000 into the Audit table, and.
• Whose yearly Income is greater than 70000 into the Employee table

-- Example for INSTEAD OF INSERT Triggers in SQL Server
USE [SQL Tutorial]
GO
CREATE TRIGGER InsteadOfINSERTTriggerExample on [EmployeeTable]
INSTEAD OF INSERT 
AS
BEGIN 
INSERT INTO [EmployeeTableAudit](
	[ID],
	[Name],
	[Education],
	[Occupation],
	[YearlyIncome],
	[Sales],
        [ServerName],
	[ServerInstanceName],
	[Insert Time])
SELECT  ID,	
        Name, 
	Education, 
	Occupation, 
	YearlyIncome,	
	Sales,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)),
	CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)),
	GETDATE()
FROM INSERTED
WHERE YearlyIncome <= 70000;
PRINT 'We Successfully Fired Our INSTEAD OF INSERT Triggers in SQL Server.';

INSERT INTO [EmployeeTable]( 
	[Name],
	[Education],
	[Occupation],
	[YearlyIncome],
	[Sales])
SELECT  Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales
FROM INSERTED
WHERE YearlyIncome > 70000;
END
GO

OUTPUT

ANALYSIS

Here, we used the INSERT Statement to insert the selected values into the Employee Table. And the following statements will return the Machine Name and the server name. This information might be helpful for the audit

SERVERPROPERTY('MachineName'), 
SERVERPROPERTY('ServerName')

Let me show you the newly created trigger by opening the Object Explorer -> Go to the SQL Tutorial Database -> Go and expand the Employee Table -> and then expand the Triggers Folder

For the demonstration purpose, we are inserting 14 records present in the Employee into the Employee table, to check whether the Instead of insert Trigger is triggered or not.

-- Example for Instead of INSERT Triggers in SQL Server
USE [SQL Tutorial]
GO
INSERT INTO [EmployeeTable] ([Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales])
  SELECT [Firstname] + ' '[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales] 
  FROM [Employee]

OUTPUT

From the above screenshot, you can see that our trigger is triggered. And, instead of inserting 14 records into the Employee table, it inserted 8 records into the Audit Table, and 6 records into the Employee table. Please use the following SQL Query to check the inserted records in the Employee table

-- Example for SQL Instead of INSERT Triggers
USE [SQL Tutorial]
GO
SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

OUTPUT

Next, check the records in the Employee table Audit using the following query.

-- Example for SQL Instead of INSERT Triggers
USE [SQL Tutorial]
GO

SELECT [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
  FROM [EmployeeTableAudit]

OUTPUT

You can also try with the conditions, something like: Insert the records into Employee table whose Yearly Income is greater than 7000 otherwise, throw an error (or message) etc.