|
|
Posted by sblar on 01/07/06 12:55
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.
Any suggestions?
Navigation:
[Reply to this message]
|