Triggers

 Triggers

According to microsoft Trigger is special type of stored procedure that automatically runs when an event occurs in the database server. 

Types of trigger 

1. DML triggers

DML triggers execute in background when DML operation performed by user. Off  course first            we need to create trigger for table,view etc..

2. DDL triggers

DDL triggers execute in background when DDL operation performed by user and tirgger                    created for that DDL command.

3. Logon Triggers

It execute in response with Logon event when user session is being established. we can create             trigger from .net framework CLI or Transact SQL.

In this post we will discuss on DML trigger and DML in next article

Suppose in organisation they want to generate automatic email if employee is on emergency leave and email suggest to apply for an leave from empoyee portal. To achieve same scenario with DML trigger, we will apply trigger on employee table and insert records into that table, once record inserted in leave table autoamtically it gets inserted into Leave_Email table and at the end of month job will send email to all employee who are on leave and delete data from that table after sending email to all employee.

Insert Trigger

Create table EmployeeLeave(

Id int identity(1,1),

Leave varchar(10), 

Name varchar(10)

)

Insert into EmployeeLeave values ('Leave', 'Sameer')

Insert into EmployeeLeave values ('Leave', 'Sam')

Insert into EmployeeLeave values ('Leave', 'Sam')

Create Table Leave_Email(

Id int identity(1,1),

Name varchar(10)

)

create trigger Leave_triiger

on EmployeeLeave

For Insert

As 

Begin

Declare @name varchar(10)

-- Special table in SQL Server which keeps track of latest data inserted

select @Name =Name from inserted

Insert Into Leave_Email values (@name)

End

select * from Leave_Email

Upadate Trigger

-- Below triiger fires when something gets changed in EmployeeLeave table

-- If any records gets updated in EmployeeLeave table, trigger fires select query and show recent updated record.

-- For an example I didn't perform any operation but bbased on scenario you can get recent data from inserted table and perform operation you want to perform

create trigger Employee_update

on EmployeeLeave

For update

As 

Begin

select *,'Record from inserted table' from inserted

End

Update EmployeeLeave

Set Name='Samurai' where id=1

Delete trigger 

-- Delete trigger on EmployeeLeave table instead of selecting record from deleted table for realy time scenario you can modify based on requirements

Create trigger Employee_Delete

on EmployeeLeave

for Delete

As

Begin

-- Special table in MS SQL server which keeps track of recent deleted record

Select *, 'Record from deleted table' from deleted

End

delete from employeeLeave where id=1

Instead of  trigger 

Instead of  trigger is part of DML trigger which allow us to insert records into View if two or more table joins involved in view.

 

First create 3 tables into data base and will create view and select data for view from 3 tables.

Create table Student

(

id int primary key identity(1,1),

name varchar(10),

city varchar(10)

)

Insert into Student values ('Noel','Pune')

Insert into Student values ('Naved','Mumbai')

Insert into Student values ('Sameer','Pune')

create table subjects

(

id int primary key identity(1,1),

name varchar(10)

)

Insert into subjects values('English')

Insert into subjects values('Maths')

Insert into subjects values('Science')

create table student_subjects

(

id int primary key identity(1,1),

studentid int foreign key references Student(Id),

subjectid int foreign key references subjects(Id)

)

Insert into student_subjects values (1,1)

Insert into student_subjects values (1,2)

Insert into student_subjects values (1,3)

Insert into student_subjects values (2,1)

Insert into student_subjects values (2,3)

Insert into student_subjects values (3,1)

Insert into student_subjects values (3,2)

Create View to hide complexity of data and tables

Create view vStudent

As

select stud.name as StudentName , sub.name as SubjectName,Stud.city  from student_subjects ss inner join student stud  on ss.studentid=stud.id inner join subjects sub on sub.id = ss.subjectid

Trigger below helps to insert data into tables from View created.

Create trigger student_Trigger 

on vStudent

instead of insert

AS

Begin

Declare @studName varchar(10)

Declare @subName varchar(10)

Declare @City varchar(10), @studId int, @subId int

select @studName = StudentName, @subName = SubjectName, @City=City from inserted

If ((select count(name) from student where name = @studName) > 0)

Begin 

select @studId = id from student where name = @studName

End

Else

BEGIN

Insert into Student values (@studName, @City)

select @studId = @@IDENTITY

END

If( (select count(id) from subjects where name=@subName) > 0 ) 

BEGIN

select @subId = id from subjects where name = @subName

END

Else

BEGIN

Insert into subjects values (@subName)

select @subId = @@IDENTITY

END

Insert into student_subjects values (@studId,@subId)

End

Insert into vStudent values ('Naved', 'Maths','Mumbai')

Insert into vStudent values ('Atul', 'Maths','Thane')

Now run below select query to see data gets updated for existing records and inserted for new record.

select * from student

select * from subjects

select * from student_subjects

select * from vStudent 

Instead of triggers also work for Update and Delete data from view.