August 14, 2003 at 9:25 pm
Hi,
To insert a new "class" into my database I must use three Insert statements. One to record the class detail, another to record the session detail (multiple inserts), and the last to record the educator session detail (multiple inserts).
If any of these three inserts fail, I need to "Rollback" the transactions.
The T-SQL I am using (below) doesnt seem to work. For example, I deliberatley modify the final insert statement to make it fail (remove the primary key field) and the stored procedure.
As expected the procedure fails, but when I re-run the proc with with ths same params, I recieve an error because I am inserting a "duplicate" into the table PE_CLASS. This means that the Rollback Transaction is not working.
Can anyone eye this code over and advise me how to do it properly????
NOTE: some code cut out... the previwe didn't show all the ode, I don't know if it will all appear.....
DECLARE @INTERRORCODE AS INTEGER
SET @INTERRORCODE = @@ERROR
IF @INTERRORCODE = 0
BEGIN TRANSACTION
IF @INTERRORCODE = 0
-- INSERT A "CLASS" RECORD INTO PE_CLASS TABLE
BEGIN
INSERT INTO PE_CLASS
(
PE_COURSE_CODE,PE_CLASS_CODE,PE_CL_STATUS_ID,PE_CLASS_DATE,PE_CLASS_SESSIONS
)
VALUES
(
@PE_COURSE_CODE,@PE_CLASS_CODE,@CLASS_STATUS,@CLASS_DTTM,@PE_CLASS_SESSIONS
)
SET @INTERRORCODE = @@ERROR
END
....snip....
IF @INTERRORCODE = 0
BEGIN
DECLARE CLIENT_SESSION_CURSOR CURSOR FOR
SELECT
SESSION_NUMBER,START_DTTM,END_DTTM,DAYS_APART
FROM
dbo.PE_CLIENT_SESSION_TEMPLATE
WHERE
TEMPL_REFNO = @TEMPLATE
SET @SESSION_DTTM = @CLASS_DTTM
OPEN CLIENT_SESSION_CURSOR
BEGIN
-- READ FIRST FROM CURSOR
FETCH NEXT FROM CLIENT_SESSION_CURSOR
INTO
@SESSION_NUMBER,@START_DTTM,@END_DTTM,@DAYS
-- LOOP THROUGH THE REMAINING RECORDS OF THE CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SESSION_DTTM = DATEADD(D, @DAYS, @SESSION_DTTM)
--SELECT @PE_COURSE_CODE, @PE_CLASS_CODE, @SESSION_NUMBER, @VENUE_REFNO, @SESSION_DTTM, DATEADD(HH,DATEPART(HH,@START_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM)), DATEADD(HH,DATEPART(HH,@END_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM))
INSERT INTO dbo.PE_SESSION
(
...snip......
)
VALUES
(
@PE_COURSE_CODE,@PE_CLASS_CODE,
@SESSION_NUMBER,@VENUE_REFNO,
@SESSION_DTTM,DATEADD(HH,DATEPART(HH,@START_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM)),
DATEADD(HH,DATEPART(HH,@END_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM))
)
-- READ NEXT FROM CURSOR
FETCH NEXT FROM CLIENT_SESSION_CURSOR
INTO
@SESSION_NUMBER,@START_DTTM,@END_DTTM,@DAYS
END
END
-- CLEAN-UP
CLOSE CLIENT_SESSION_CURSOR
DEALLOCATE CLIENT_SESSION_CURSOR
SET @INTERRORCODE = @@ERROR
END
IF @INTERRORCODE = 0
BEGIN
IF NOT ISNULL(@PAY_NUMBER, '') = ''
BEGIN
DECLARE EDUCATOR_SESSION_CURSOR CURSOR FOR
SELECT
... snip.....
FROM
DBO.PE_EDUCATOR_SESSION_TEMPLATE EDSSS INNER JOIN
DBO.PE_CLIENT_SESSION_TEMPLATE CLSSS ON
EDSSS.CLSSS_REFNO = CLSSS.CLSSS_REFNO
WHERE
EDSSS.TEMPL_REFNO = @TEMPLATE
ORDER BY
SESSION_NUMBER
SET @SESSION_DTTM = @CLASS_DTTM
OPEN EDUCATOR_SESSION_CURSOR
BEGIN
-- READ FIRST FROM CURSOR
FETCH NEXT FROM EDUCATOR_SESSION_CURSOR
INTO
... snip.......
-- LOOP THROUGH THE REMAINING RECORDS OF THE CURSOR
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SESSION_DTTM = DATEADD(D, @DAYS, @SESSION_DTTM)
INSERT INTO dbo.PE_ROSTER
(
... snip.....
)
VALUES
(
@PE_COURSE_CODE,
@PE_CLASS_CODE,
@SESSION_NUMBER,
@PAY_NUMBER,
1,
'N',
DATEADD(HH,DATEPART(HH,@START_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM)),
DATEADD(HH,DATEPART(HH,@END_DTTM),DATEADD(MI,DATEPART(MI,@START_DTTM),@SESSION_DTTM))
)
-- READ NEXT FROM CURSOR
FETCH NEXT FROM EDUCATOR_SESSION_CURSOR
INTO
... snip.........
END
END
-- CLEAN-UP
CLOSE EDUCATOR_SESSION_CURSOR
DEALLOCATE EDUCATOR_SESSION_CURSOR
END
SET @INTERRORCODE = @@ERROR
END
IF @INTERRORCODE = 0 --AND @@TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
raiserror('The class insert has failed. Please contact the system administrator!', 16,1)
END
go
August 14, 2003 at 9:41 pm
Solved.
I was't careful enough about where I placed SET @INTERRORCODE = @@ERROR, the error code was being overwritten (perhaps the dealloation of the cursor)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply