Database - TRIGGERS

Trigger is a special type of store procedure which will "fire"

automatically. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements.

3 main types of triggers are:

      1. Insert Trigger

      2. Update Trigger

      3. Delete Trigger

create table empsiva(empno int,ename varchar(20))

insert into empsiva values(1,'Vinoth')

insert into empsiva values(2,'Ranbhir')

insert into empsiva values(3,'Raju')

insert into empsiva values(4,'Kumar')

select *from empsiva

delete  from empsiva

'Insert Trigger'

     -trigger for insert statement

create trigger Emp_trs

on empsiva

for insert

As

if (select count(*) from empsiva a, inserted b where a.empno= b.empno)>1

Begin

      Print ' Employee No. Already Exist'

    rollback Transaction

End

   -a trigger emp_trns is created above.it executes automatically when an

insert statement is executed for that table.

insert into empsiva values(4,'karthik')

insert into empsiva values(5,'sachin')

drop trigger emp_trs

the above program does not allow duplicate values to be inserted.

'Delete Trigger'

create table empsiva1(empno int,ename varchar(20))

insert into empsiva1 values(1,'siva')

insert into empsiva1 values(2,'Ranbhir')

insert into empsiva1 values(3,'Imrankhan')

insert into empsiva1 values(4,'Karthik')

Create trigger emp_tr_del

on empsiva

for delete

as

declare @rowdel integer

select @rowdel  = count(*) from deleted

if @rowdel > 1

      Begin

            print 'More Than One Record Can not be deleted'

            Rollback Transaction

      End

else

      Begin

            print ' Record deleted Successfully'

           

      End

select * from empsiva1

delete from empsiva1 where empno=2

drop trigger emp_tr_del      

the above program does not allow to delete.

'Update Trigger'

create table empsiva2(empno int,ename varchar(20))

insert into empsiva2 values(1,'siva')

insert into empsiva2 values(2,'Ranbhir')

insert into empsiva2 values(3,'Imrankhan')

insert into empsiva2 values(4,'Karthik')

Create trigger emp_tr_upds

on empsiva2

for update

as

if update(empno)

Begin

      Print ' Employee No Can not be Modified'

      Rollback Transaction

End

update empsiva2 set empno = 2  where ename = 'siva'

update empsiva2 set ename = 'Jerome'  where empno= 2

select * from empsiva2

drop trigger emp_tr_ins

drop trigger emp_tr_del

the above program does not allow the table to update.