instead of insert trigger in sql server on views

Trigger on view using INSTEAD OF INSERT

To Create INSTEAD OF INSERT triggers in sql server 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 Create table 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's into tblDeparment table

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')

--Create another table 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)
)able

--Insert data into tblEmployee table

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.

Now create Views.

create View VwTblEmployeeJoinWithtblDepartment
as
select tblEmployee.Id, Name, Gender, DeparmentName
from tblEmployee
join tblDeparment
on tblEmployee.DeparmentId = tblDeparment.Id

Now create 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 tables and views are given below with data.

All table for INSTEAD OF INSERT using Views

Now Insert data into view "VwTblEmployeeJoinWithtblDepartment", but not updatable because affect multiple base table, so use INSTEAD OF INSERT Trigger.

insert into VwTblEmployeeJoinWithtblDepartment values('Jhon', 'Male', 'IT') 

insert row into views

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

create trigger trtblEmployeeInsteadOfInsertByView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF INSERT
As
 BEGIN
     declare @DepartId int
     select @DepartId =tblDeparment.Id from tblDeparment
     join inserted
     on inserted.DeparmentName = tblDeparment.DeparmentName    --DeparmentName of inserted(here from view) = DeparmentName of tblDeparment
     
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @EmpMessage varchar(200);
     select  @EmpName = I.Name, 
             @EmpGender= I.Gender, @EmpDepatmentName= I.DeparmentName
             from inserted I              --Here inserted table keep data which we want insert through view.
      
       if(@DepartId is Null)
         Begin
           Raiserror('Invalid Department Id', 16, 1);
           Print 'Invalid Department Id' 
           RollBack
         End   
      Else
         Begin
          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)  --16 seberaty level(there are several seberaty level) means user can't correct or resubmit the query
              RollBack
             End
           Else
             insert into tblEmployee(Name,Gender,DeparmentId)                   
                               values(@EmpName,@EmpGender,@DepartId)
                               
             /*insert into VwTblEmployeeJoinWithtblDepartment values(8,'Jhon3', 'Male3', 'IT')      
             you cant update using view return error is not updatable because the modification affects multiple base tables*/
             
            /*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 insert 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
 END

Now Insert data into view "VwTblEmployeeJoinWithtblDepartment" (which is combination of two table “tblemployee” and “tbldepartmet” which is enter connected with foreign key) with Garbage value for “DepartmentName” which is not in “tblDepartment” table so return a message invalid.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'GarbageValue') --id=1 insert but it is false because view require id and table “tblEployee” has id is identity. so table automatically set increment id.

inter row into views with garbage value

Now insert with correct data into “VwTblEmployeeJoinWithtblDepartment”, which is available in “tblDepartment” table.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'HR') 

audit data of Instead of Insert using views

Example 2
For more understand about INSTEAD OF INSERT trigger on view, when multiple base table update so we take another trigger example.

First we create another 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 on views for “tblEmployee”.

create trigger trtblEmployeeInsteadOfInsertByDetailView
ON VwTblEmployeeJoinWithtblDepartment
INSTEAD OF INSERT
As
 BEGIN
     Declare @DepartId int
     Select @DepartId= tblDeparment.Id from tblDeparment
     join inserted
     on inserted.DeparmentName = tblDeparment.DeparmentName    
     
     Declare @EmpId int, @EmpName varchar(30), 
             @EmpGender varchar(8), @EmpDepatmentName varchar(20),
             @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, @EmpDepatmentName= I.DeparmentName
            from inserted I      --Here inserted table keep data which we want insert through view.
      if(@DepartId is Null)
         Begin
           Raiserror('Invalid Department Id', 16, 1);
           Print 'Invalid Department Id' 
           RollBack
         End      
      Else
        Begin
          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,@DepartId)
            /*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, @DepartId, @AuditAction,
                                           @ActionTakenBy, GETDATE(), @ServerName, @ServerInstanceName)
             PRINT 'Successfully Insert Data with ID=' + @EmpId + CAST(Getdate() AS nvarchar(30));
             PRINT 'Fired the INSTEAD OF INSERT Triggers in SQL Server'
            End
         End
 END

Now Insert data into view “VwTblEmployeeJoinWithtblDepartment” with Garbage value for “DepartmentName” which is not in “tblDepartment” table so return an error message.

insert into “VwTblEmployeeJoinWithtblDepartment” values(1,'Jhonnifer', 'Female', 'GarbageValue') --id=1 insert but it is false because view require id and table “tblEployee” has id is identity. so table automatically set increment id.
--If enter already Exists name than through an error message also.

insert data into views with garbage value

Now insert with correct data, which is available in “tblDepartment” table.

insert into VwTblEmployeeJoinWithtblDepartment values(1,'Jhonny', 'Male', 'HR') 

Now check tables and views.

Audit Details when insert data in view using Instead of insert trigger

previous Related Post Next Related Post