9 September 2010

Trigger in Sql

Create Trigger For Insert
ALTER trigger [dbo].[trinsert]
on [dbo].[emp]

after insert
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='inserted'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)


Trigger For After Delete

ALTER trigger [dbo].[trdelete]
on [dbo].[emp]

after delete
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from deleted)
set @depid=(select depid from deleted)
set @fname=(select fname from deleted)
set @lname=(select lname from deleted)
set @gender=(select gender from deleted)
set @dob=(select dob from deleted)
set @doj=(select doj from deleted)
set @email=(select email from deleted)
set @city=(select city from deleted)
set @astatus='deleted'
set @Dateandtime=getdate()
set @salary=(select salary from deleted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

Trigger For Update
ALTER trigger [dbo].[trupdate]
on [dbo].[emp]

after update
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='updated'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

No comments:

Post a Comment

Comments Welcome