INSERT 是否会导致触发器“插入”多行？桌子？
我知道在触发器中 - 至少对于 SQL Server - 人们永远不应该假设插入的表只有一行，这意味着触发器中这样的 SQL 通常是不好的：
select @UserID = ID from inserted
但是出于好奇，一组 INSERT 语句是否可以导致插入多行的表？我知道更新很容易，但从我的测试来看，我无法模拟插入的类似结果。我尝试在发送批处理终止符之前发送多组插入，例如：
insert into TriggerTest (col2) select 'a'
insert into TriggerTest (col2) select 'b'
insert into TriggerTest (col2) select 'c'
go
并将它们包装在事务中：
begin tran
insert into TriggerTest (col2) select 'a'
insert into TriggerTest (col2) select 'b'
insert into TriggerTest (col2) select 'c'
commit
但它总是会导致触发器在 1 行的 inserted 表中触发 3 次，并且从来没有一次插入过 3 行的插入表格。
这对我来说完全有意义（毕竟它们是 3 个独立的语句），而且我不需要实际执行它，我只是想知道单独的 INSERTS 是否可以有不同的行为。
编辑：这是一个愚蠢的问题：当然可以，在插入结果集时！
insert into TriggerTest (col2) select 'a' union select 'b'
...或任何其他类型的集合。
请原谅，现在已经是凌晨 3 点了。我将把这个问题留给那些应该更了解的人。
I know that within a trigger - at least for SQL Server - one should never assume that the inserted table has just one row, which means SQL like this in a trigger is usually bad:
select @UserID = ID from inserted
But out of curiosity, can a set of INSERT statements ever result in an inserted table of more than one row? I know it's easy enough with an UPDATE, but from my tests I can't simulate a similar result for INSERTs. I've tried sending across sets of inserts before sending the batch terminator, e.g:
insert into TriggerTest (col2) select 'a'
insert into TriggerTest (col2) select 'b'
insert into TriggerTest (col2) select 'c'
go
And also wrapping them in transactions:
begin tran
insert into TriggerTest (col2) select 'a'
insert into TriggerTest (col2) select 'b'
insert into TriggerTest (col2) select 'c'
commit
But it will always result in the trigger fired 3 times with an inserted table of 1 row, and never one time with an inserted table of 3 rows.
That completely makes sense to me (they are 3 separate statements after all), and I don't need to actually do it, I'm just wondering if INSERTS alone can ever behave differently to this.
Edit: this is a dumb question: of course it can, when inserting a result set!
insert into TriggerTest (col2) select 'a' union select 'b'
... or any other sort of set.
Forgive me, it is almost 3AM here. I'll leave this question here for people who should know better anyway.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试
try