Transactional T-SQL Problem

  • 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

  • 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