Scott's posting didn't make it here (I found it on Google), so I'm adding
my replies to David's pos.
David Portas (RE****************************@acm.org) writes:
1. SELECT PrimaryKeyColumn FROM INSERTED
SELECT * FROM INSERTED
Both of these seems wrong. Trigger should not normally return result sets.
Sometimes you want to do this for debugging, but leaving it around can
cause great confusion, because the client that executed the INSERT/UPDATE/
DElETE statment is not expecting a result set, and not consuming the
result set, can lead to the trigger not completing, the transaction not
being committed, and locks being held blocking other users.
All that said, I can't see any difference in effeciecy in this case,
since "inserted" is a virtual table, and there are no indexes.
And that leads to a question of trigger performance you didn't ask: if
your trigger makes several references to the inserted/deleted tables,
consider doing this first in your trigger:
INSERT @inserted (...)
SELECT ... FROM inserted
and then use the table variables in the following queries. The virtual
tables are slow, because they are read from the transaction log.
It might be tempting to do:
SELECT * FROM #inserted FROM inserted
since you don't have to declare a copy of the table, but since triggers
are always run in a transaction context, this can be costly, since the
creation of the temp table is logged. (Believe me, this is something I've
learnt the hard way.)
2. Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE?
Try to keep trigger logic simple and set-based - you may be able to
write a single DML statement rather than use multiple IFs in one
trigger. If the logic is quite different in each case then create
separate triggers for each action.
Scott's question is certainly meaningful. I often write a combined
INSERT/UPDATE trigger, because the same validations has to be performed.
For the same reason, I also include the DELETE action for the same trigger.
But some checks or cascading actions may only perform to one of them. In
that case it appears to be good for performance to not run that
check/action.
The way to check is:
IF EXISTS (SELECT * FROM @inserted) AND NOT (SELECT * FROM @deleted)
-- We have an INSERT statement.
3. Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
Probably. Although, I prefer to check SELECT COUNT(*) on inserted/deleted.
This may be due to that in earlier versions of SQL Server, @@rowcount did
not always have the right number.
Why would you care if one or multiple rows were updated? Triggers should
use set-based code so the logic should be identical in any case. Never
assume that only single rows will be updated - this is a classic error
and one that causes a lot of grief judging by posts in this group.
There are indeed cases where you want to differentiate between a
singlerow/multirow operation:
o The primary key is updated, and you need to relate the old and new
value. Unless the table has an alternate key which is left unchanged,
you cannot handle multirow updates in this case. The one way out is
to disallow multi-row updates, by checking @@rowcount.
o The trigger logic for the general case for multi-row updates is much
slower than specific single-row logic, and you expect single-row
updates to be the most common. In this case you check @@rowcount,
and take different paths depending on the value. We have at least one
such trigger in our system.
o A variation of this is that the multi-row handling is complicated, and
there is only a requirement to handle single-row operation. After all,
most development budgets are limited. Of course, in this case too, you
need to check @@rowcount, and raise an error in case of a multi-row
operation.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
