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.IdNow 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 |
