Triggers_Basics in sql sever

TRIGGERS

create database yjreddy

create table EmployeeTrigger

                           (

                           Eid int,

                           Ename varchar(50),

                           Address varchar(50),

                           Salary int

                           )

insert into EmployeeTrigger values(101,'yjreddy','Banglore',36000),(102,'Maheshredy','Hyderabad',26000),(103,'janibhasha','Guntur',25000),(104,'adi','hyd',25000),(105,'srikar','banglore',42000)

select * from EmployeeTrigger

--after insert trigger

create trigger afterinserttrigger on employeetrigger

for insert

as

declare @empid int;

declare @ename varchar(50);

declare @address varchar(50);

declare @salary int;

declare @action varchar(100);

select @empid=i.Eid from inserted i;

select @ename =i.Ename from inserted i;

select @address=i.Address from inserted i;

select @salary=i.Salary from inserted i;

set @action='after insert trigger fired'

insert into employeetrigger(Eid,Ename,Address,Salary)

values(@empid,@ename,@address,@salary)

print 'after insert trigger fired' +  convert(varchar(50),getdate());

insert into EmployeeTrigger values(106,'anil','hyderabad',42000)

--after update trigger

 create trigger updatetrigger1 on employeetrigger

 for update

 as

 declare @empid int;

 declare @ename varchar(50);

 declare @address varchar(50);

 declare @salary int;

 declare @action varchar(50);

 select @empid=i.Eid from inserted i;

 select @ename=i.Ename from inserted i;

 select @address=i.Address from inserted i;

 select @salary =i.Salary from inserted i;

 if update(Ename)

 set @action='updated record in the name field--after update trigger';

 if update(Salary)

 set @action='updated record in the salary field--after update trigger';

 insert into employeetrigger(Eid,Ename,Address,Salary)

 values(@empid,@ename,@address,@salary);

 print 'after update trigger fired'

 update  employeetrigger SET  salary=42000 where ADDRESS='BANGLORE'

 select * from employeetrigger

DROP TRIGGER UPDATETRIGGER

--after delete trigger

create trigger deletetrigger on employeetrigger

for delete

as

declare @empid int;

declare @ename varchar(50);

declare @address varchar(50);

declare @salary int;

declare @action varchar(50);

select @empid=d.Eid from deleted d;

select @ename =d.Ename from deleted d;

select @address=d.Address from deleted d;

select @salary=d.salary from deleted d;

set @action='Records deleted';

                          

 insert into employeetrigger(Eid,Ename,Address,Salary)

 values(@empid,@ename,@address,@salary);

 print 'Records are deleted from table'

 delete from employeetrigger where Ename='anil'

--instead of trigger

--instead delete

create trigger insteaddelete on EmployeeTriggertableinstead

instead of delete

as

declare @empid int;

declare @ename varchar(50);

declare @address varchar(50);

declare @salary int;

declare @action varchar(50);

select @empid=d.Eid from deleted d;

select @ename =d.Ename from deleted d;

select @address=d.Address from deleted d;

select @salary =d.Salary from deleted d;

begin

if(@salary> 40000)

begin

RAISERROR('cant delete records where salary is more than 40000',16,1);

ROLLBACK;

end

else

begin

delete from EmployeeTriggertableinstead where Eid=@empid;

commit;

insert into EmployeeTriggertableinstead(Eid,Ename,Address,Salary) values(@empid,@ename,@address,@salary)

print 'record deleted instead of trigger'

end

end

delete from EmployeeTriggertableinstead where Eid=104     

can we write both triggrs on the same table?

NO