> 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***@here.dk',
>         @TO        = N'***@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.

> 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.

"Erland Sommarskog" <esq***@sommarskog.se> skrev i en meddelelse
news:Xns9744C51E0DD29Yazorman@127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dpor***@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 Søren
> 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!
>

> 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?

"Erland Sommarskog" <esq***@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?

"Erland Sommarskog" <esq***@sommarskog.se> skrev i en meddelelse
news:Xns9745F24504C83Yazorman@127.0.0.1...
> 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.
>