Cursor loop is broken

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 on 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?
This is the flow:
-- SP1 --
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
OPEN csrListe
-- The first record is treated here...
:
-- Treat the rest
WHILE @.@.FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @.feltet
IF @.@.FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
(@.Ordrenr, @.Status, @.Dato, @.Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe
-- Table2_ITrig --
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
-- 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)
SET XACT_ABORT ON
IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'me@.here.dk',
@.TO = N'you@.here.dk',
@.priority = N'HIGH',
@.subject = N'Status error',
@.message = N'Status error',
@.type = N'text/plain',
@.server = 'smtp.here.dk'
RETURN
END
The mail is send so it must be the final RETURN that is causing the
trouble.sblar wrote:
> 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 on 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?
> This is the flow:
> -- SP1 --
> DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
> OPEN csrListe
> -- The first record is treated here...
> :
> -- Treat the rest
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> FETCH NEXT FROM csrListe INTO @.feltet
> IF @.@.FETCH_STATUS = 0 BEGIN
> blah-blah-blah
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
> (@.Ordrenr, @.Status, @.Dato, @.Result)
> END
> END
> CLOSE csrListe
> DEALLOCATE csrListe
> -- Table2_ITrig --
> INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
> -- 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)
> SET XACT_ABORT ON
>
> IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
> SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
> UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
> EXEC @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'me@.here.dk',
> @.TO = N'you@.here.dk',
> @.priority = N'HIGH',
> @.subject = N'Status error',
> @.message = N'Status error',
> @.type = N'text/plain',
> @.server = 'smtp.here.dk'
> RETURN
> END
> The mail is send so it must be the final RETURN that is causing the
> trouble.
Do not use cursors in triggers. Doubly important, do not send email
from a trigger.
See:
http://groups.google.co.uk/group/mi...b273c91159441f2
David Portas
SQL Server MVP
--|||Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/S=F8ren|||"Sren Larsen" <sblar1@.surfpost.dk> wrote in message
news:1136633487.767264.30440@.g49g2000cwa.googlegroups.com...
Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/Sren
First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
statement instead:
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
SELECT Ordrenr, Status, Dato, Resultat
FROM ... ?
I expect there was more processing that you left out of your post but I can
only suggest a solution for what you posted.
Secondly, you need to modify your trigger to handle multiple rows properly.
Example:
/* This will FAIL if more than one row is inserted/updated */
SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
If you don't send emails from the trigger then you won't need to assign the
column values to variables.
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
> statement instead:
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
> SELECT Ordrenr, Status, Dato, Resultat
> FROM ... ?
> I expect there was more processing that you left out of your post but I
> can only suggest a solution for what you posted.
> Secondly, you need to modify your trigger to handle multiple rows
> properly.
> Example:
> /* This will FAIL if more than one row is inserted/updated */
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> If you don't send emails from the trigger then you won't need to assign
> the column values to variables.
Hey, I've already said all of that! (Except the point of not sending
mail from a trigger.) But I said it in a different newsgroup, as Sren
posted the message independently to two newsgroups. With the result
that I and David waste our time to say the same thing.
Please do not do that again!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks David.
The cursor is in the sp not the trigger. I understand your point about
email though, and I will consider another path. But, this doesn't seem
to be the problem here as the email is sent.|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Anyway, thanks for your answers. I'm aware of the problem if my trigger
> receives multiple rows, which it dont cause its only called from my SP
> with the cursor loop. I would however very much like to avoid this way
> of processing but I can't see how if I want to do some processing on
> each row inserted. For example:
> if Status = 1
> set @.result = 2
> if Status = 2
> set @.result = 3
> update sometable set Status = @.Status where number = select number from
> inserted
> Any suggestions?
Without knowledge of the business problem, it's difficult to suggest a
complete solution. But for the particular problem you appear to illustrate
you can use the CASE expression:
UPDATE a
SET result = CASE b.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN b ON a.col = b.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> skrev i en meddelelse
news:Xns9745A1206A4E9Yazorman@.127.0.0.1...
> Without knowledge of the business problem, it's difficult to suggest a
> complete solution. But for the particular problem you appear to illustrate
> you can use the CASE expression:
> UPDATE a
> SET result = CASE b.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN b ON a.col = b.col
>
Aha - thats neat. Does this mean that inserted are traversed and a.result
would be updated for every row in inserted and can it be done more than once
like this?
UPDATE a
SET result = CASE inserted.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN inserted ON a.col = inserted.col
UPDATE c
SET somefield = CASE inserted.someotherfield
WHEN 1 THEN 11
WHEN 2 THEN 12
WHEN 3 THEN 13
ELSE 0
END
FROM c
JOIN inserted ON c.col = inserted.col
Will this do an update of both a and c based on inserted rows?
/Sren|||I almost forgot; why is the cursorloop broken in the first place? There was
no error in trigger, unless RETURN is considered an error!
/Sren|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Aha - thats neat. Does this mean that inserted are traversed and
> a.result would be updated for every row in inserted and can it be done
> more than once like this?
> UPDATE a
> SET result = CASE inserted.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN inserted ON a.col = inserted.col
> UPDATE c
> SET somefield = CASE inserted.someotherfield
> WHEN 1 THEN 11
> WHEN 2 THEN 12
> WHEN 3 THEN 13
> ELSE 0
> END
> FROM c
> JOIN inserted ON c.col = inserted.col
> Will this do an update of both a and c based on inserted rows?
Yes. CASE is extremely powerful when working with set-based operations.
The above is a simplifed form. The more general form is like this:
CASE WHEN somecolumn IN (1, 2, 3) THEN 'This'
WHEN othercolumn = 'A' AND somecolumn = 34 THEN 'That'
WHEN somecolumn < 0 THEN CASE col WHEN 1 THEN 'J' ELSE 'N' END
END
So you can test for more general conditions, and you can nest CASE.
The conditions are always evaluated top-down, and evaluation stops
as soon one matches. If no condition is true, and there is on ELSE,
the value is NULL.
Important to understand is that CASE is an *expression*, and as a
an expression it always return the same data type. If you try:
CASE something WHEN 1 THEN 0 ELSE 'x' END
this will fail, when something is not 1, because this CASE expression
returns an integer value, as integer is higher than char in the
data-type precedence in SQL Server.

> I almost forgot; why is the cursorloop broken in the first place? There
> was no error in trigger, unless RETURN is considered an error!
Was there a ROLLBACK? A rollback in a trigge aborts execution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx