How to create Trigger in Sql Server (Part 1)

What is Trigger in Sql Server…………………

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

Type of Trigger: In Sql Server there are three type of triggers

1. DML Trigger
2. DDL Trigger
3. Logon Trigger

DML Trigger:  DML Trigger are tried automatically in response to DML events (insert, delete, update)

DML triggers can be classified into two ways

1. After trigger
2. Instead of Triggers

After trigger fires after the triggering action The insert, update, delete statements causes an after trigger to fire after the respective statements complete execution 

Instead of triggers to fires instead of   triggering action The insert, update, delete statements causes an Instead of trigger to fire instead of the respective statements execution

creating database tests 
create database tests 
after creating database tests ,create table  Customer with using database tests 
use tests
create table customer (id int primary key IDENTITY(1,1),Name varchar(50),FatherName nvarchar(50)) 

after creating tabel customer ,create trigger tr1  on Customer table ,when user insert any data  in table  Customer  message printed on screen 'Data  inserted'

create trigger tr1 on customer

after insert

as

print 'Data  inserted'

insert into customer values('Kush Tiwari','Jai Prakash Tiwari')

create trigger tr2  on Customer table ,when user insert any data  in table  Customer then  tempory table inserted  on  Sql Server screen  

alter trigger tr2 on customer

after insert

as

print 'Inserted Table'

select * from inserted

print 'Deleted Table'

select * from deleted

Note :  What is  temporary table ?

Answer:  When  you insert  any  data (row ) in any table ,thereafter  two temporary tables  are  generated  by Sql      Server  By  Default .The  temporary table  are as follows:

 1.   Inserted  Table

      2.   Deleted Table

         When  we insert   record  in my table they  are  passed in inserted temporary  table till      we  don’t  commite

      And  both temporary  table i.e.  inserted  table  and  deleted table are  save in Log file

delete from customer where id=8

Note  In this concept we going to create when ever we want to insert any record in student table  with RollNo, Name,Phy,Chem,Math after inserting student automatically insert RollNo and Total marks like (Math+Phy+Chem) in marks table using Trigger concept

create database tests

-- create table Student

create table Student

(

RollNo int primary key,

Name varchar(50),

Phy int,

Chem int,

Math int

)

-- create table Marks

create table marks

(

RollNo int primary key,

total int

)

-- create trigger tr1

create trigger tr1 on student

after insert

as

begin

declare @RollNo int

declare @phy int

declare @Chem int

declare @Math int

declare @tt int

select @phy=i.Phy,@Chem=i.Chem,@Math=i.Math,@RollNo=i.RollNo from Student i

set @tt=(@phy+@chem+@Math)

insert into marks values(@RollNo,@tt)

end

insert into Student values(1,'Kush Tiwari',23,67,45)

select * from Student

select * from marks

Result 

For example:

-- create table tb_Student......................

create table tb_Student(RollNo int primary key,Name varchar(50) unique,Gender varchar(50),CourseId int)

-- create table tb_StudentAudit......................

create  table tb_StudentAudit(SrNo int identity(1,1),AuditData varchar(max))

-- create trigger on tb_Student  which name is tr_tbstudent_forinsert................

create trigger tr_tbstudent_forinsert

on tb_Student

for insert

as

begin

declare @id int

select @id=RollNo from inserted

insert into tb_StudentAudit

values ('New Student with RollNo = '+ CAST(@id as varchar(5))+' '+'is added at'+' '+ CAST(GetDate() as varchar(35)))

end

-- create trigger on tb_Student  which name is tr_tbStudentForDelete............

create  trigger tr_tbStudentForDelete

on tb_Student

for Delete

as

begin

declare @id int

select @id=RollNo from deleted

insert into tb_StudentAudit

values ('An existing Student with RollNo = '+ CAST(@id as varchar(5))+' '+'is deleted  at'+' '+ CAST(GetDate() as varchar(35)))

end

insert into tb_Student values(3,'Lav Kumar','Male',8)

select * from tb_Student

select * from tb_StudentAudit

delete from tb_stu where RollNo=3

Result