使用SQLServer中的更新触发器更新多行 [英] Multiple rows update using update trigger in SQLServer

问题描述

如果我在employee表中添加包含默认值的新列，例如

if I Add new column at employee table which contains default value like

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

例如：emp001 xxxx 10000.00支付

emp002 yyyy 12000.00支付

emp003 zzzz 10000.00支付

emp004 aaaa 15000.00支付

emp005 bbbb 12000.00付款

emp006 cccc 12000.00付款

emp007 pppp 10000.00付款



更新员工定薪= 11000.00 where salary = 10000.00

更新后第一次更新行设置其默认值但是休息行不在employee表中设置它。请尽快给我一个解决方案。

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 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.

推荐答案

您好，



可能是以下代码可以工作为你...



USE [MyTestDB]

GO

/ ******对象：Trigger [dbo]。[UpdateEmployee]脚本日期：05/04/2015 17:34:43 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER触发器[dbo]。[更新员工] [员工]
$ b更新后的$ b AS

开始

声明@empid为[varchar]（10）

将@empname声明为[varchar]（50）

声明@salary为[decimal]（18,2）

将@status声明为[varchar]（100）

将@empids声明为[varchar]（max）



从删除的d中选择@empid = d.empid

选择@salary = d.salary从已删除的d



print @empids

print @empid



如果更新（状态）

开始

选择@ status = i.status从删除i

--print'if'

结束

其他

开始

- 打印'其他'

选择@ status = i .status来自删除我

- 选择*来自[员工]其中empid in（SELECT empid FROM inserted）

update [employee]

设置状态=''其中empid in（SELECT empid FROM inserted）

end

--print'已删除'

- 从*中选择*删除

插入employeeHistory（[EffectDate]，[empid]，[empname]，[salary]，[status]）

SELECT getdate（），empid，empname，工资，状态

FROM FROM

end

Hi,

May be bellow code will work for you...

USE [MyTestDB]
GO
/****** Object: Trigger [dbo].[UpdateEmployee] Script Date: 05/04/2015 17:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[UpdateEmployee] on [dbo].[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)
declare @empids as [varchar](max)

select @empid =d.empid from deleted d
select @empname =d.empname from deleted d
select @salary =d.salary from deleted d

print @empids
print @empid

if update(status)
begin
Select @status=i.status from deleted i
--print 'if'
end
else
begin
--print 'else'
select @status=i.status from deleted i
--select * from [employee] where empid in (SELECT empid FROM inserted)
update [employee]
set status='' where empid in (SELECT empid FROM inserted)
end
--print 'deleted'
--Select * from deleted
Insert into employeeHistory([EffectDate],[empid],[empname],[salary],[status])
SELECT getdate(), empid, empname, salary,status
FROM deleted
end

这篇关于使用SQLServer中的更新触发器更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！