Sql Server Trigger - Instead of insert trigger

To Create INSTEAD OF INSERT Triggers first of all we need to create a table (but we create two table) for which we apply trigger and need another table that hold all activity in main table.

--We cretate tblDeparment table
create table tblDeparment
(
 Id int identity(1,1) primary key not null,
 DeparmentName varchar(30) not null,
 Location Char(8) not null,
 DeparmentHead varchar(20) not null
)

--Insert data into tblDeparment tables

Insert into tblDeparment values('IT','London','Carrie')
Insert into tblDeparment values('HR','Mexico','Toy')
Insert into tblDeparment values('Purchasing','Delhi','Ravi')
Insert into tblDeparment values('Finance','New York','Janie')

--We cretate tblEmployee table

create table tblEmployee(
Id int identity(1,1) primary key not null,
Name varchar(30) not null,
Gender Char(8) not null,
DeparmentId int null constraint FK_tblEmployee_DeparmentId foreign key(DeparmentId) references tblDeparment(Id)
)

--Insert data into tblEmployee tables

insert into tblEmployee values('Jane', 'Female', 1) 
insert into tblEmployee values('Levis', 'Male', 3)
insert into tblEmployee values('Lee', 'Female', Null)
insert into tblEmployee values('Rose', 'Female', 1)

Here we create two table “tblDeparment” and “tblEmployee “ which are inter-connected through Foreign key constraint.
We will apply trigger for these tables.

Example 1

Now create audit table that hold activity of main table by user.

create table tblAudit
(
Id int primary key identity(1,1) not null,
AuditData nvarchar(500)
) 

We have table are given below with data's.

we have tables for INSTEAD OF INSERT Trigger

Now create sql server audit table trigger for INSTEAD OF INSERT Trigger on “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsert
ON tblEmployee
INSTEAD OF INSERT
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(200);
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentId= I.DeparmentId
            from inserted I
      
     If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
       Begin
           SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
           Raiserror(@EmpMessage, 16, 1)
           RollBack
       End
       Else
          Begin
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@EmpDepatmentId)
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write iinsert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             insert into tblAudit values('Employee succefully insert with Id ' + CAST(@EmpId as varchar(5)) + 'in trigger in date' + CAST(Getdate() AS nvarchar(30)))
          End
 END

Now try to insert data into "tblEmployee".
If we try to insert Name which is already exists than return message Employee with name already exits

insert into tblEmployee values('Jane', 'Male', 4) 

INSTEAD OF INSERT Trigger fire if already exists row, than return message

Now we try to insert Name which is not already exists return message succefully insert.

insert into tblEmployee values('Jhon', 'Male', 4)

Now check data from tables.

Row insert into Audit table when fire INSTEAD OF INSERT Trigger

Example 1

For more understand about INSTEAD OF INSERT Trigger we take another ms sql trigger example. First we create another audit table that hold all activity of main table done by user.

create table tblAuditDetailsBy
(
  Id int not null Primary key identity(1,1),
  EmpId int Not Null,
  EmpName varchar(30) Not Null,
  EmpGender char(8) Not Null,
  EmpDeparmentId int Not Null,
  AuditAction varchar(1000) Not Null,
  ActionTakenBy varchar(50) Not Null,
  AuditTime datetime not null default Getdate(),
  ServerName varchar(100) Not Null,
  ServerInstanceName varchar(100) Not Null
)

Now create the INSTEAD OF INSERT Trigger for “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsertByDetail
ON tblEmployee
INSTEAD OF INSERT
As
 BEGIN
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentId int,
             @EmpMessage varchar(200),@AuditAction varchar(2000),
             @ActionTakenBy varchar(100),@AuditTime varchar(50),
             @ServerName varchar(100), @ServerInstanceName Varchar(100);
             
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentId= I.DeparmentId
            from inserted I
      
     If(exists(Select Name from tblEmployee where Name = @EmpName))   --If Alredy Name Exists in Cloumn than through message we can also check by UserName, EmailId etc.
       Begin
           SET @EmpMessage  = 'Employee With Name ' + @EmpName + 'alredy Register with this Name'
           Raiserror(@EmpMessage, 16, 1);
           PRINT 'INSTEAD OF INSERT Triggers in SQL Server not fire because ' + @EmpMessage
           RollBack
       End
       Else
          Begin
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@EmpDepatmentId)
            /*If we commented Above line and than try to insert data into tblEmployee message return
             row affected but basically does not insert data in tblEmployee because of INSTEAD OF TRIGGER 
             and in tblAudit table NULL value will be insert because there is no last generated SCOPE_IDENTITY().
             So if you want to insert than must write iinsert querry here.*/                               
             select @EmpId=SCOPE_IDENTITY() from tblEmployee;
             SET @AuditAction='Insert Record'
             SET @ActionTakenBy= SYSTEM_USER
             SET @ServerName = CAST(SERVERPROPERTY('ServerName') as varchar(100))
             SET @ServerInstanceName = CAST(SERVERPROPERTY('MachineName') as varchar)
             insert into tblAuditDetailsBy(EmpId, EmpName, EmpGender, EmpDeparmentId, AuditAction,
                                           ActionTakenBy, AuditTime, ServerName, ServerInstanceName)
                                    values(@EmpId,@EmpName, @EmpGender, @EmpDepatmentId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Insert Data with ID=' + CAST(@EmpId as varchar(5)) + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF INSERT Triggers in SQL Server'
          End
 END

Now try to insert data into "tblEmployee".
Now we try to insert Name which is not already exists return message successfully insert.

insert into tblEmployee values('Jhon', 'Male', 4) 

INSTEAD OF INSERT Trigger fire when insert row

Now check data from database triggers in sql tables.

Insert row into audit table when fire INSTEAD OF INSERT Trigger