|
|
Posted by Erland Sommarskog on 01/07/06 14:03
Søren Larsen (sblar1@surfpost.dk) writes:
> In a stored procedure (SP1) I am looping through a cursor with records
> from Table1. Each record in the cursor is inserted into Table2.
> Insert trigger on Table2 is inserting the record into Table3 (in
> another DB).
> In the insert trigger on Table3, a series of checks are done on the
> inserted record and in case of an error, an email is sent and the
> trigger returns.
> This break the cursorloop in SP1 and the rest of the records in the
> cursor is not treated.
> How do I make sure that all records are treated?
An error in a trigger aborts the batch. Thus, in SQL 2000, there is no
way to handle the situation in T-SQL, you would need to have a client
program that reacts on the error, and restarts the loop, but leaving
out the row that causes problems. In SQL 2005, you could use TRY-CATCH
to handle the situation.
But why are you running a loop in the first place? The normal procedure
to insert rows from one table to another is to say:
INSERT tbl2 (...)
SELECT ...
FROM tbl1
Of course, this would mean that if any of the rows are erroneous, then
all rows inserted would be rolled back by the trigger on Table3. But this
can be handled in different ways. (But exactly how, it's difficult to
say as I don't know the business requirements.)
The reason you should insert all, and not run a cursor, is that performance
for a cursor can be disastrous. If we are talking less than < 100 rows, it's
may be not that big deal. If we are talking 10000 rows, it can mean a
difference in processing time of 30 minutes instead of 30 seconds.
> -- Table3_ITrig ---------------------------------
> SET NOCOUNT ON
>
>
> DECLARE @STATUS int
> DECLARE @DATOTID smalldatetime
> DECLARE @RESULT int
>
>
> SELECT @ORDRENR = (SELECT ORDRENR FROM INSERTED)
> SELECT @STATUS = (SELECT STATUS FROM INSERTED)
> SELECT @DATOTID = (SELECT DATO FROM INSERTED)
> SELECT @RESULT = (SELECT RESULT FROM INSERTED)
This trigger is poorly implemented. A trigger fires once per statement,
and must be able to handle multi-row operations.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|