Insted Of Trigger Table Variable Derived Table

Insted Of Trigger Table Variable Derived Table about this you will able to understand in this article By Sagar Jaybhay In SQL Server.

Before read this article read below article First to better understanding.

Part 1: –

Part 2:-

Insted Of Trigger

Instead Of Update Trigger

Instead of an update trigger is similar to instead of insert trigger. It is fire when we are going to update the records in view and in below code, you will see I am using Update function which we get the value true or false if we trying to set that parameter value in our case we are trying to deptname so update(deptname) return true.

alter trigger tr_updateempdec
on vw_empdec
instead of Update
declare @deptname nvarchar(20);
declare @deptid int;

select @deptname=deptname from inserted;
print('department is '[email protected])

select @deptid=tbldept.deptid from tbldept join
inserted on tbldept.deptname=inserted.deptname;

if(@deptid is null)
raiserror('dept is null ',16,1)

update tblEmp set [email protected] from inserted join tblEmp on;


update vw_empdec set deptname='Hr' where id=1;

above is the query of our view which we trying to update.

Instead of Delete trigger

This triggers also the same which is fire when someone tries to delete records from view.

Insted Of Delete Trigger
Insted Of Delete Trigger

When you trying to delete records from view then we get the following error

Msg 4405, Level 16, State 1, Line 6

View or function ‘vw_empdec’ is not updatable because the modification affects multiple base tables.

create trigger tr_deletetrigger
on vw_empdec
instead of delete

delete from tblEmp
where in (select id from deleted)

delete from vw_empdec where id=1;

For avoiding this error we create above delete trigger which works perfectly fine.

Table Variable In SQL Server

It is like a temporary table and table variable also created in tempdb

The scope of table variable in batch, stored procedure and in the statement of blocks in which it is declared.

Also, table variables can be passed as parameters.

Below is the syntax of the table variable.

When you use table variable you need to define the data type and column name in that table.

declare @tabledemo table(departname varchar(20),empcount int);

insert into @tabledemo
select d.Department_Name, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

select * from @tabledemo where empcount>100;

Derived Table In SQL Server

This derive tables like a temporary table or table variable but it is available only in the context of the current query.

Remember when you derived a table you need to give a name for that table else it is not working.

If you trying to access this derived table in another select statement outside the context of this query then it is not possible.

select deptname, empcount
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
) as derivedtable 

Derived Table In SQL Server
Derived Table In SQL Server

GitHub Link:-

Sagar Jaybhay

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *