一个利用SQL触发器的问题.请帮忙!谢谢!

[edplrh2006]

我有一个触发器,如果Effect字段为1时,就更新另一张表的Salary字段.但为何有多条记录的Effect字段都被更新为1时,却只更新一条Salary记录呢?触发器如下:
CREATE trigger tg_raises
on dbo.Raises
for insert,update
as
declare @Employeeid int
declare @Effect int
declare @NewSalary int

select @NewSalary=NewSalary,@Effect=Effect,@Employeeid=Employeeid from inserted
if @Effect=1
begin
update employeesalary set Salary=@NewSalary where Employeeid=@Employeeid
end

试过不要Employeeid=@Employeeid,但这样的话,只要有一条记录的Effect更新为1时,所有记录的Salary都会被更新

[edplrh2006]

怎样啊?谁告诉我怎么修改啊?

[中国风]

update employeesalary set Salary=@NewSalary
where Employeeid=@Employeeid--这里一定要加条件限制
inserted 有没有列在Employeeid是唯一对应的

[hellowork]

CREATE trigger tg_raises on dbo.Raises
for insert,update
as
update a set Salary = i.NewSalary from employeesalary as a
inner join inserted as i on a.Employeeid = i.Employeeid
where i.Effect = 1
GO

注意:下面这行代码只能读取一行
select @NewSalary=NewSalary,@Effect=Effect,@Employeeid=Employeeid from inserted
如果有批量更新或插入的情况请不要这样写,应该与inserted表进行内连接.

[zsl5305256]

select @NewSalary=NewSalary,@Effect=Effect,@Employeeid=Employeeid from inserted
这只能取到最后一条符合的记录,所以就只理新一条了!
update a set Salary = i.NewSalary from employeesalary as a
inner join inserted as i on a.Employeeid = i.Employeeid
where i.Effect = 1

[edplrh2006]

正解!謝謝!