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.