I know that in a trigger - at least for SQL Server - you cannot assume that the inserted table has only one row, which means that SQL in this trigger is usually bad:
select @UserID = ID from inserted
But out of curiosity, can a set of INSERT statements ever lead to the inserted table of more than one row? I know this quite easily with UPDATE, but from my tests, I cannot simulate a similar result for INSERT. I tried sending over multiple inserts before sending the batch terminator, for example:
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 this will always cause the trigger to fire three times with the inserted table of 1 row and never with the inserted table of 3 rows.
It totally makes sense to me (after all, these are 3 separate statements), and I don’t need to do this, I just wonder if only INSERTS can behave differently.
Edit: this is a stupid question: of course, when inserting a result set!
insert into TriggerTest (col2) select 'a' union select 'b'
... or any other type of kit.
Forgive me, it's almost 3AM here. I will leave this question here for people who should still know.