开发者

Can an INSERT result in more than one row in a trigger "inserted" table?

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 t开发者_如何学运维hem 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.


try

insert into TriggerTest (col2) 
select 'a'
union 
select 'b'
union
select 'c'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜