views:

31

answers:

1

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.

+2  A: 

try

insert into TriggerTest (col2) 
select 'a'
union 
select 'b'
union
select 'c'
Ed Harper
Yeah, realised how silly I was immediately after posting the question. Well, free points for you.
Gavin Schultz-Ohkubo