inserted 和 deleted 表里永远只有一条记录吗？

[jskscxy]

inserted 和 deleted 表里永远只有一条记录吗？

如果批量删除或批量更新的话 inserted 和 deleted 表里也只有一条记录吗？

[勿勿]

批量操作的时候应该是有多条的。

[cd731107]

多条相同货品group by后对数量合计，做为一条再对库存进行处理

[jskscxy]

[Quote=引用 12 楼 jyh070207 的回复:]

SQL code

if exists(select 1 from inserted where bdid = 12) and not exists(select 1 from deleted) --insert
update tpDingDanMX
set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
from (select i.ddmx_rowid,sum(i……
[/Quote]
这个update触发器计算方法不对啊！

[jskscxy]

if exists(select 1 from inserted where bdid = 12) and exists (select 1 from deleted) --update
begin
update tpDingDanMX
set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
from
(select t.ddmx_rowid,sum(isnull(t.sl,0)) as sl
from (select i.ddmx_rowid,sum(isnull(i.sl,0)) as sl
from inserted i
group by i.ddmx_rowid
union all
select d.ddmx_rowid,sum(isnull(d.sl,0)) * (-1) as sl
from deleted i
group by d.ddmx_rowid) t
group by t.ddmx_rowid) b
where tpDingDanMX.rowid = b.ddmx_rowid

end

这个update触发器计算方法不对啊！

[--小F--]

是一条记录

[AcHerat]

不是。

[NBDBA]

不是，认为只有一条是触发器常见错误之一

[水族杰纶]

[Quote=引用 1 楼 fredrickhu 的回复:]
是一条记录
[/Quote]
肿么可能

[--小F--]

[Quote=引用 4 楼 wufeng4552 的回复:]
引用 1 楼 fredrickhu 的回复:
是一条记录

肿么可能
[/Quote]


激动了 把不字打掉了

[NBDBA]

[Quote=引用 5 楼 fredrickhu 的回复:]
引用 4 楼 wufeng4552 的回复:
引用 1 楼 fredrickhu 的回复:
是一条记录

肿么可能



激动了 把不字打掉了
[/Quote]
别急嘛，砖石总是你的

[jskscxy]

既然不是一条记录

那如果出库表或入库表批量删除5条记录，触发器如何写才能让库存表里的相对应产品减去或增加库存呢？

[水族杰纶]

[Quote=引用 7 楼 jskscxy 的回复:]
既然不是一条记录

那如果出库表或入库表批量删除5条记录，触发器如何写才能让库存表里的相对应产品减去或增加库存呢？
[/Quote]
根据条件啊

[jyh070207]

[Quote=引用 7 楼 jskscxy 的回复:]
既然不是一条记录

那如果出库表或入库表批量删除5条记录，触发器如何写才能让库存表里的相对应产品减去或增加库存呢？
[/Quote]
将inserted及deleted视为表一样处理就可以了,
有多条可以用group by 一下,大致如下.

update 库存表
set 库存数=库存数 - t.出库数
from (select i.产品编码,sum(i.出库数) as 出库数
	from inserted i
	group by i.产品编码)t
where t.产品编码= 库存表.产品编码

[jskscxy]

if exists(select 1 from inserted where bdid = 12) and not exists(select 1 from deleted) --insert
		update tpDingDanMX set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
			from tpDingDanMX a join inserted b on a.rowid = b.ddmx_rowid

	if exists(select 1 from inserted where bdid = 12) and exists (select 1 from deleted)    --update
	begin
		if exists(select 1 from inserted a join deleted b on a.rowid = b.rowid where a.sl <> b.sl)
			update tpDingDanMX set sl_ = isnull(sl_,0) + (isnull(b.sl,0)- isnull(c.sl,0))
				from tpDingDanMX a join inserted b on a.rowid = b.ddmx_rowid
								   join deleted  c on b.rowid = c.rowid
	end

	if not exists(select 1 from inserted where bdid = 12) and exists (select 1 from deleted where bdid = 12) --delete
	update tpDingDanMX set sl_ = isnull(sl_,0) - isnull(b.sl,0)
				from tpDingDanMX a join deleted b on a.rowid = b.ddmx_rowid

我这样写对不对呀？

[jyh070207]

[Quote=引用 10 楼 jskscxy 的回复:]
SQL code

if exists(select 1 from inserted where bdid = 12) and not exists(select 1 from deleted) --insert
update tpDingDanMX set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
from tpDin……
[/Quote]
还是没有考虑到多行的情况,即一个inserted或一个deleted有多个ddmx_rowid

[jyh070207]

if exists(select 1 from inserted where bdid = 12) and not exists(select 1 from deleted) --insert
update tpDingDanMX 
set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
from (select i.ddmx_rowid,sum(isnull(i.sl,0)) as sl
	from inserted i
	group by i.ddmx_rowid) b
where tpDingDanMX.rowid = b.ddmx_rowid



    if exists(select 1 from inserted where bdid = 12) and exists (select 1 from deleted)    --update
    begin
	update tpDingDanMX 
	set sl_ = isnull(sl_,0)+ isnull(b.sl,0)
	from 
		(select t.ddmx_rowid,sum(isnull(t.sl,0)) as sl
		from 	(select i.ddmx_rowid,sum(isnull(i.sl,0)) as sl
			from inserted i
			group by i.ddmx_rowid
			union all
			select d.ddmx_rowid,sum(isnull(d.sl,0)) * (-1) as sl
			from deleted i
			group by d.ddmx_rowid) t
		group by t.ddmx_rowid) b
	where tpDingDanMX.rowid = b.ddmx_rowid

    end

    if not exists(select 1 from inserted where bdid = 12) and exists (select 1 from deleted where bdid = 12) --delete
	update tpDingDanMX 
	set sl_ = isnull(sl_,0) - isnull(b.sl,0)
	from (select d.ddmx_rowid,sum(isnull(d.sl,0)) as sl
		from deleted i
		group by d.ddmx_rowid) b
	where tpDingDanMX.rowid = b.ddmx_rowid
	

[-晴天]

批量插入或批量更新时,会有多条.