What is the Triggers in SQL and how to utilized the Triggers .

Triggers:- Trigger works like store procedure and function at the point when it is called or fire between    two or multiple tables and it does automatically..

Triggers are 3 types:- DML, DDL & Logon.

1. DML Triggers.

   This type of trigger fires automatically in such a events (INSERT, UPDATE & DELETE ).
    DML triggers are two types:-
    1. After of trigger.
    2. Instead of trigger.

After of trigger:- execution happens when primary table statement get executed first and then secondly  triggered  table executed  in concern of (INSERT, UPDATE & DELETE ) operation .
or in other sentances
After trigger fire after the execution query of an action that can be ddl statement or dml statement.

Instead of trigger :- Instead of trigger fire before the execution query of an action that can be ddl statement or dml statement. it will not effect. Through Insteadof trigger we can insert  the data  multiple table views.

Example After trigger.

create trigger EmpAudit 
on [dbo].[Employees]
for insert
as
begin
declare @Empno int
select @Empno = [Empno] from inserted  // inserted is a magic table create by sql to hold the data.
insert into [dbo].[Audit] values('Employee no :-'+Convert(varchar,@Empno) + '.Registered on '+CONVERT(nvarchar,GETDATE()))
end

Now insert the new row Employee table,

insert into   [dbo].[Employees]values( 9999, 'PRANEET', 'MANAGER', 78
Convert(datetime,'1981-05-01'),   2850, null, 30  )

execute and row added to the employee table.
Audit table will look where after trigger had fired.

1	Employee no :-9999.Registered on Jan 27 2018  5:26PM.

Same case in delete

create trigger DeleteEmpAudit 
on [dbo].[Employees]
for delete
as
begin
declare @Empno int
select @Empno = [Empno] from deleted
insert into [dbo].[Audit] values('Employee no :-'+Convert(varchar,@Empno) + '.Deleted on '+CONVERT(nvarchar,GETDATE()))
end

delete from [dbo].[Employees] where [Empno] = 9999

2	Employee no :-9999.Deleted on Jan 27 2018  5:44PM

Same case in Update

we use  both the operation deleted and inserted
select * from deleted //  it delete the old data
select * from inserted // it contain fresh records.


Instead of Trigger program

>creating the view

Create view [dbo].[CustomerDerpartmentVw]
as

select  Empno,Ename, Dept.Dname from [dbo].[Employees] join Dept on Employees.Deptno=Dept.Deptno

> creating the  Instead of Trigger

  Create trigger InsteadofInsertvw
on [dbo].[CustomerDerpartmentVw]
Instead of Insert
as
begin
declare @Deptno int
select @Deptno = Deptno  from [dbo].[Dept]  join 
            inserted on inserted.Dname=Dept.Dname 

insert into [dbo].[Employees](Empno,Ename,Deptno)
select Empno,Ename, @Deptno from inserted
end

>Execution
insert into 

[dbo].[CustomerDerpartmentVw] values(2212,'Amit','Sales')

Output:- in Emp table

2212

Amit

NULL

NULL

NULL

NULL

NULL

30

 View

2212

Amit

SALES

   

Author : praneet kumar

Share this