Before read this article read below article First to better understanding.
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;
GitHub Link:- https://github.com/Sagar-Jaybhay