Easy question - how to use the Trans (Commit and Rollback)in this way? 2

[RovirozM]

Hi Guys!

I would like to know how to use the "Begin Trans" (Commit and Rollback) Process because is the first time that I'm going to use one.... (and I don't want to block anything of course)

I have a Stored Procedure that do two steps:

In the first step Insert/update one table (Master) and in the second step Insert/update another table (Detail).

When my SP runs... both has to be consistent (I mean if the first table is updated and in the other one that is second step find an error I would like to Rollback everything)

Any recommendation to do this guys?

Thanks a Lot !

MR

 

[bborissov]

Something like that:

...
DECLARE @MyError int
BEGIN TRANSACTION
-- Update/Insert/Delete in first table
SET @MyError = @@ERROR
IF @MyError = 0
   BEGIN
     -- Update/Insert/Delete in second table
     SET @MyError = @@ERROR
   END

IF @MyError = 0
   COMMIT
ELSE
   ROLLBACK

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Rhys666]

If you're using SQL Server 2005 or later TRY...CATCH is a better methodology to learn. I.E.,

BEGIN TRY
    BEGIN TRANSACTION;
        -- Update/Insert/Delete in first table
        -- Update/Insert/Delete in second table
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 0, 1 or -1.

    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- XACT_STATE = -1 means there is a transaction and it is
    -- uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- XACT_STATE = 1 means there is a transaction and it is
    -- active and valid. You can COMMIT or ROLLBACK here
    -- based on your requirements.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'I'm rolling it back anyway due to the error.'
        ROLLBACK TRANSACTION;
    END;
END CATCH;

MSDN

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[ousoonerjoe]

There's more than one way to skin that kitty:

BEGIN TRY
	BEGIN TRANSACTION;
		UPDATE....
		UPDATE....	
	COMMIT TRANSACTION;
BEGIN CATCH
	IF @@TRANCOUNT > 0 
		BEGIN
			ROLLBACK TRANSACTION;
		END
	--ErrLog table.
	INSERT INTO ErrLog (Number, Severity, ErrState, 
			ErrSource, ErrLine, ErrMsg, Form, AppID)
		SELECT ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0), 
			ISNULL(ERROR_PROCEDURE(),'Unknown.'), ISNULL(ERROR_LINE(),0), 
			ISNULL(ERROR_MESSAGE(),'Failed to Select Payables.'), OBJECT_NAME(@@PROCID), 1;
	
	SELECT [ErrorNumber] = ISNULL(ERROR_NUMBER(),-1), 
			[ErrorMessage] = ISNULL(ERROR_MESSAGE(),'Payables falied to Select.'), 
			[ErrorLine] = ISNULL(ERROR_LINE(),0), 
			[ProcName] = OBJECT_NAME(@@PROCID), 
			[AppID] = 1,
			[IdNum] = @VenNum;
END CATCH

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[RovirozM]

Hi Guys,

Good News !! It is working good....

It is possible to send an Email when an error is found?

BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
<--- Send an Email ?
ROLLBACK TRANSACTION;
END

Thanks !

 

[ousoonerjoe]

Take a look at this other resent thread. It should point in the direction you are looking for.

thread183-1651667

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------