In this article I will explain with simple example, how to write an Insert Of Insert Trigger in SQL Server.
This tutorial is applicable for all versions of SQL Server i.e. 2005, 2008, 2012, 2014, etc.
Database
I have made use of the following table Customers with the schema as follows.
![SQL Server: Instead Of Insert Trigger Example]()
I have already inserted few records in the table.
![SQL Server: Instead Of Insert Trigger Example]()
Below is the CustomerLogs table which will be used to log the Trigger actions.
![SQL Server: Instead Of Insert Trigger Example]()
Note: You can download the database table SQL by clicking the download link below.
Instead Of Insert Triggers
These triggers are executed instead of any of the Insert, Update or Delete operations.
For example consider an Instead of Trigger for Insert operation, whenever an Insert is performed the Trigger will be executed first and if the Trigger inserts record then only the record will be inserted.
Below is an example of an Instead Of Insert Trigger. Whenever anyone tries to insert a row from the Customers table the following trigger is executed.
Inside the Trigger, I have added a condition that if a record with the Name already exists then such a record must not be inserted and an error must be raised. Also a record is inserted in the CustomerLogs table.
If a record with the Name does not exists in the table then an insert query is executed which inserts the record and a record is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_InsteadOfINSERT]
ON [dbo].[Customers]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT, @Name VARCHAR(50), @Country VARCHAR(50)
DECLARE @Message VARCHAR(100)
SELECT @CustomerId = INSERTED.CustomerId,
@Name = INSERTED.Name,
@Country = INSERTED.Country
FROM INSERTED
IF (EXISTS(SELECT CustomerId FROM Customers WHERE Name = @Name))
BEGIN
SET @Message = 'Record with name ' + @Name + ' already exists.'
RAISERROR(@Message, 16 ,1)
ROLLBACK
END
ELSE
BEGIN
INSERT INTO Customers
VALUES(@CustomerId, @Name, @Country)
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'InsteadOf Insert')
END
END
The following error message shown when record with existing Name is inserted
![SQL Server: Instead Of Insert Trigger Example]()
The following screenshot displays the Log table after the Instead Of Insert Trigger is executed.
![SQL Server: Instead Of Insert Trigger Example]()
