主表rdrecord
id handler
984 aaa子表 rdrecords
auid 主表id cinvcode unitcost
1 984 0101 12
2 984 0201 24
存货表inventory
cinvcode cinvdefine1
0101
0201select *
from rdrecord a
left join rdrecords b on a.id=b.id
left join inventory c on b.cinvcode=c.cinvcode
where a.id=984查询我是可以查到两条记录的我在触发器时,我想在主表的handler字段有字符写入时,就触发,子表中相应的存货的UNITCOST写入到存货表相应的存货的CINVDEFINE1中去CREATE TRIGGER [GHY] ON [dbo].[RdRecord]
FOR INSERT, UPDATE, DELETE
AS
begin
declare @id as int
declare @Vencode as varchar(50)
declare @Handler as varchar(50) select @id = id ,@Handler=CHandler from inserted
if LEN(@Handler)<>0
begin
update c
set c.cinvdefine1=b.iunitcost
from rdrecord a
left join rdrecords b on a.id=b.id
left join inventory c on b.cinvcode=c.cinvcode
where a.id=@id
end
end
但运行结果发现,只更改了最后一行记录,这是为什么呢?触发器应怎样写呢?
id handler
984 aaa子表 rdrecords
auid 主表id cinvcode unitcost
1 984 0101 12
2 984 0201 24
存货表inventory
cinvcode cinvdefine1
0101
0201select *
from rdrecord a
left join rdrecords b on a.id=b.id
left join inventory c on b.cinvcode=c.cinvcode
where a.id=984查询我是可以查到两条记录的我在触发器时,我想在主表的handler字段有字符写入时,就触发,子表中相应的存货的UNITCOST写入到存货表相应的存货的CINVDEFINE1中去CREATE TRIGGER [GHY] ON [dbo].[RdRecord]
FOR INSERT, UPDATE, DELETE
AS
begin
declare @id as int
declare @Vencode as varchar(50)
declare @Handler as varchar(50) select @id = id ,@Handler=CHandler from inserted
if LEN(@Handler)<>0
begin
update c
set c.cinvdefine1=b.iunitcost
from rdrecord a
left join rdrecords b on a.id=b.id
left join inventory c on b.cinvcode=c.cinvcode
where a.id=@id
end
end
但运行结果发现,只更改了最后一行记录,这是为什么呢?触发器应怎样写呢?
CREATE TRIGGER [GHY] ON [dbo].[RdRecord]
FOR INSERT, UPDATE, DELETE
AS
begin
declare @id as int
declare @Vencode as varchar(50)
declare @Handler as varchar(50) --select @id = id ,@Handler=CHandler from inserted
--if LEN(@Handler) <>0
--begin
update c set c.cinvdefine1=b.iunitcost
from (select * from rdrecord as t1 inner join inserted as t2 on t1.id=t2.id and len(t2.CHandler)>0 ) as a
left join rdrecords b on a.id=b.id
left join inventory c on b.cinvcode=c.cinvcode
--where a.id=@id
--end
end
FOR INSERT, UPDATE, DELETE
AS
begin
--declare @id as int
--declare @Vencode as varchar(50)
--declare @Handler as varchar(50) --select @id = id ,@Handler=CHandler from inserted
--if LEN(@Handler) <>0
--begin
update c
set c.cinvdefine1=b.iunitcost
from rdrecord a
join rdrecords b on a.id=b.id and len(a.CHandler)<>0
join inventory c on b.cinvcode=c.cinvcode --即然要更新的是inventory,就没必要用左连接,用内连效率更高.
--where a.id=@id
--end
end
貌似原来的触发器不应该只更新一条数据?这个触发器的
FOR INSERT, UPDATE, DELETE -- DELETE操作未作任何处理