Instead of Insert Trigger in SQL Server

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained the implementation about how to create an Instead of Insert Trigger in SQL Server. This article is applicable for all versions of SQLServer i.e. 2005, 2008, 2012, 2014, 2019, 2022 etc.

Database Details

I created the following table named userdetail with the all details as follows.

 Column name         data Type

 userid                      int

 username               varchar(100)

 useremail               varchar(50)

useraddress            varchar(250)

I have already inserted few records in the table.

userid          username             useremail                             useraddress

1                  Sanjeev                  sanjeev@gmail.com       #250 Sector 127, Chandigarh

2                  Sanjay                     sanjay@gmail.com         #123 Sector 255, Chandigarh

3                  Ruhika                    ruhika@gmail.com         #256 Sector 855, Chandigarh

4                  Rajan                      rajan@gmail.com            #55 Sector 00, Chandigarh

 

I created another table userlogin with the mentioned columns as follows.

loginid                     userid                 Action

Below I am giving the complete SQL Script for both tables. You need to execute the complete script for creation of both tables in the database with the record inserted.

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[userdetails](

            [userid] [int] IDENTITY(1,1) NOT NULL,

            [username] [varchar](100) NOT NULL,

            [useremail] [varchar](50) NOT NULL,

            [useraddress] [varchar](250) NOT NULL,

 CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [userid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

CREATE TABLE [dbo].[userlogin](

            [loginid] [int] IDENTITY(1,1) NOT NULL,

            [userid] [int] NOT NULL,

            [ACTION] [varchar](50) NOT NULL,

 CONSTRAINT [PK_userlogin] PRIMARY KEY CLUSTERED

(

            [loginid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

INSERT INTO Customers

SELECT 'Sanjeev', 'sanjee@gmail.com', ‘#250 Sector 127, Chandigarh’

UNION ALL

SELECT 'Mudassar Khan', 'India'

UNION ALL

SELECT 'Ruhika', 'ruhika@gmail.com', ‘#256 Sector 855, Chandigarh’

UNION ALL

SELECT 'Rajan', 'rajan@gmail.com', ‘#55 Sector 00, Chandigarh’

 

GO

Instead Of Insert Triggers

In SQL Server, an "INSTEAD OF INSERT" trigger allows you to intercept an INSERT statement on a table or, more commonly, a view, and execute alternative logic instead of the default insert operation. This means the actual INSERT statement on the target table or view is skipped, and the code defined within the trigger is executed instead. These triggers are executed instead of any of the Insert, Update or Delete operations.

Here I am giving an example, and using an Instead of Trigger for Insert command on the table, when an Insert command will be execute then the Trigger will also be executed first and if the Trigger inserts record then only the record will be inserted.

Check the example of Instead Of Insert Trigger. When any new one will be try to insert a row from the userdetails table the following trigger will be executed.

In the Trigger, I am using code for a condition also, if a record with the Name will be already exists in the table then new record will not be inserted in the table and an error message will be occurred. Also a record will be inserted in the userlogin table.

If a record with the entered Name by the use will not exists in the table, then an insert query will be executed and new record will be inserted in the userlogin table.

 

CREATE TRIGGER [dbo].[userdetails_InsteadOfINSERT]

       ON [dbo].[user]

INSTEAD OF INSERT

AS

BEGIN

       SET NOCOUNT ON;

 

       DECLARE @userid INT, @usrname VARCHAR(50), @useremail VARCHAR(50),     

      @useraddress VARCHAR(250),

       DECLARE @Message VARCHAR(100)

 

       SELECT @ userid = INSERTED. userid,

               @username = INSERTED.username,

               @useremail = INSERTED.useremail, 

              @useraddress = INSERTED.useraddress      

       FROM INSERTED

 

       IF(EXISTS(SELECT userid FROM userdetails WHERE Name = @username))

       BEGIN

              SET @Message = 'Record with name ' + @ username + ' already exists in the Table.'

              RAISERROR(@Message, 16, 1)

              ROLLBACK

       END

       ELSE

       BEGIN

              INSERT INTO userdetails

              VALUES(@username, @useremail)

 

              INSERT INTO userlogin

              VALUES(@userid, 'InsteadOf Insert')

       END

END

Conclusion: In above code, I have been explained implementation about how to how to create an Instead of Insert Trigger in SQL Server. This code is very helpful for every developer. Bye bye and take care of yourself Developers. We will come back shortly with the new article.

 

 

Regards

Using Asp.net