TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
rITTWIK MISHRA
NA
1
812
Multiple rows update using update trigger in SQLServer
Apr 29 2015 5:03 PM
create table employee(empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))
create table employeeHistory(EffectDate [datetime],empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))
create trigger UpdateEmployee on employee
after update AS
begin
declare @empid as [varchar](10)
declare @empname as [varchar](50)
declare @salary as [decimal](18, 2)
declare @status as [varchar](100)
select @empid =d.empid from deleted d
select @empname =d.empname from deleted d
select @salary =d.salary from deleted d
if update(status)
select @status=i.statusfrom deleted i
else
begin
select @status=i.status from deleted i
update [employee]
set status='' where empid=@empid
end
Insert into employeeHistory([EffectDate],[empid],[empname],[salary],[status])
SELECT getdate(), empid, empname, salary,status
FROM deleted
end
Example:
emp001 xxxx 10000.00 paid
emp002 yyyy 12000.00 paid
emp003 zzzz 10000.00 paid
emp004 aaaa 15000.00 paid
emp005 bbbb 12000.00 paid
emp006 cccc 12000.00 paid
emp007 pppp 10000.00 paid
update employee set salary =11000.00 where salary =10000.00
After execute above query the employee returns
emp001 xxxx 10000.00
emp002 yyyy 12000.00 paid
emp003 zzzz 10000.00 paid
emp004 aaaa 15000.00 paid
emp005 bbbb 12000.00 paid
emp006 cccc 12000.00 paid
emp007 pppp 10000.00 paid
That is after update first updated row set its default value but rest rows don't set it in employee table. Please give me a solution at your earliest.
Reply
Answers (
1
)
Compare and find match record
Insert multiple records using stored procedure