Hi all,

I'm writing a bunch of triggers for an off-the-shelf product to
enforce some business rules not enforced by the standard product. Not
ideal I know but its what I have to do.

The application does not allow more than 1 record to be inserted at a
time into any table. However, someone has just alerted me to the fact
that an another application that we will be interfacing to may insert
multiple rows at a time.

I have written all my triggers based on the INSERTED table only
containing one record. Is this going to cause problems? Can you
explain (generally) how this will cause problems, considering I want
my trigger to act upon every single record inserted.

Thanks,

Always write triggers that perform correctly when multiple rows are
insterted. Even if inserting multiple rows isn't a requirement today, sooner
or later you'll find that your trigger is a limitation (for example if you
need to do some administrative maintenance to data without using your
application). If you don't do it now you'll only end up re-writing the code
one day. There's simply no good reason for writing triggers that can only
handle a single row.


[quoted text, click to view]

That depends on your trigger code and what your business logic is.

--
David Portas
SQL Server MVP
--

What David said. In addition, there is a high probability that someone will
execute an statement that affects multiple rows at some time in the future
without realizing that this type of action is "not supported". Guess who
gets to clean up the data WHEN IT IS DISCOVERED?

To answer the "impact" question. The trigger will perform the appropriate
actions using the values of a single row. All other rows will be ignored.
In addition, there is no guarantee that the same logical row (first, last,
etc) will be processed during each invocation of the trigger. So if your
trigger prevents the insertion of rows that violate condition "x", the
trigger will only enforce this condition against the row it evaluates
(within the set of rows that were affected by the statement). If the
evaluated row is acceptable, then all others are implicitly accepted.

[quoted text, click to view]

Variable assignment in a SELECT statement is the culprit. The pros and cons
of assigment in a SELECT statement have been discussed at length before but
that single ill-conceived (IMO) feature must be to blame for an awful lot of
faulty triggers. Without it, developers would be much less inclined to make
this mistake and the problem would likely show up faster when they did.

--
David Portas
SQL Server MVP
--

Thank you all very much fopr your explaination.

Am I right in saying then that the only way to cater for multiple
inserts etc is to have a cursor that selects each row from the
INSERTED table and does whatever needs to be done to each row?

I've read many people advising against using cursors in triggers but I
have little choice.

Cheers,

No, you don't need to use a cursor. Always avoid cursors if you can,
especially in triggers. A poorly performing trigger could become a
bottleneck that blocks other updates.

Lookup the CREATE TRIGGER statement in Books Online. It has some examples of
how to use the INSERTED and DELETED virtual tables to handle multiple rows.
Or post DDL and your existing code so that someone can help you write a
set-based alternative.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

In most cases you don't use a cursor, you use set based SQL statements.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


[quoted text, click to view]

Yes. That is what is being said. If you have to do a row by row operation
on the rows in the trigger, this is the only way. There are valid reasons
to do this (a good example would be a third party system that has a
procedure to post some transaction that has hundreds of lines of spaghetti
code (mmmmm, spaghetti!) that you don't want to have to re-engineer)

[quoted text, click to view]

I feel it my civic duty to say a big "Amen" to this. Only in extreme cases
is it a necessity, most times it is laziness. Obviously you have to decide
for yourself which is the case :)

--
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]