Learn Step By Step By Sagar Jaybhay: 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: - https://sagarjaybhay.com/what-is-triggers-in-sql-sagar-jaybhay-part-1/

Part 2:-https://sagarjaybhay.com/update-trigger-instead-of-trigger-sagar-jaybhay/

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
as
begin
declare @deptname nvarchar(20);
declare @deptid int;

select @deptname=deptname from inserted;
print('department is '+@deptname)

if(UPDATE(deptname))
begin
select @deptid=tbldept.deptid from tbldept join
inserted on tbldept.deptname=inserted.deptname;

if(@deptid is null)
begin
raiserror('dept is null ',16,1)
return
end;

update tblEmp set deptid=@deptid from inserted join tblEmp on tblEmp.id=inserted.id;
end

end;


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.

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
as
begin

delete from tblEmp
where tblEmp.id in (select id from deleted)
end;

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
from
(
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 
where 
empcount>100;