Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Transactions, Commit and Rollback in Stored Procedures

    Hi,

    I have a stored procedure that does an insert in three different tables...

    If the insert in TABLE1 fails, then it needs to exit.
    Then, if the insert in TABLE2 fails, it needs to undo the 1st insert and exit.
    Then, if the insert in TABLE3 fails, it needs to undo the 1st and 2nd insert and exit.

    How do I go about this? Also I want to print a message if any insert fails, rollback and then exit. Thank you!

    Here's what my code looks like so far -->

    CREATE PROCEDURE PROC_IN ( param1 VARCHAR(36), IN param2 VARCHAR(64), IN param3 INTEGER, IN param4 INTEGER)
    RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN
    --if this fails, then print error message and exit
    INSERT INTO TABLE1 VALUES (............);

    -- if this fails, then undo TABLE1 insert, print error message and exit
    INSERT INTO TABLE2 VALUES (............);

    --if this fails, then undo TABLE1 and TABLE2 insert, print error message and exit
    INSERT INTO TABLE3 VALUES (............);

    END
    @
    Last edited by db2user; 10-15-08 at 20:01.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just use savepoints and signal/error handlers. When you enter the SP, you set a savepoint. With the error handler, you roll back to the savepoint in case one of those insert operations didn't succeed.

    p.s: What do you mean with "print error message"? Print it to where? Since a stored procedure runs on the server, it usually doesn't make any sense to print something to a console there. Thus, you can either return error information via an OUT parameter of the procedure, via another table, or by writing it to a log file from the SP. Writing to a log file is an FAQ and you may want to use your favorite search engine for that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    thanks for the reply...sorry I meant assign a message to an OUT parameter, not print. I will try out the savepoint..how can I check if the insert fails? I looked at all of the default EXCEPTIONS and I can't see any that corresponds to failing inserts in the documentation. thank you!!

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can capture all SQLEXCEPTION or SQLERROR conditions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    thank you.. I got it to work... handled all the SQLEXCEPTIONS.

  6. #6
    Join Date
    Apr 2009
    Posts
    2
    Code:
    CREATE PROCEDURE [dbo].[spInsertCustomer]
    (
    	@CustomerID char(10),
    	@CustomerName varchar(50),
    	@Address varchar(50),
    	@City char(20),
    	@ContactPerson varchar(50),
    	@ParentID char(10),
    	@Operator char(10),
    	@Modifier timestamp OUTPUT,
    	@Jan money,
    	@Feb money,
    	@Mar money,
    	@Apr money,
    	@Mei money,
    	@Jun money,
    	@Jul money,
    	@Agt money,
    	@Sep money,
    	@Okt money,
    	@Nov money,
    	@Des money
    )
    AS
    BEGIN 
    
    	SET NOCOUNT ON
    	
    	DECLARE @Err1 int, @Err2 int	
    	BEGIN TRANSACTION
    	
    		INSERT INTO Customer(CustomerID,CustomerName,Address,City,ContactPerson,ParentID,Operator)
    		VALUES(@CustomerID,@CustomerName,@Address,@City,@ContactPerson,@ParentID,@Operator);
    		
                              -- Set @@Error to variable if an error occure in second query
    		SET @Err1 = @@ERROR
    		
    		INSERT INTO CustomerTarget(CustomerID,Jan,Feb,Mar,Apr,Mei,Jun,Jul,Agt,Sep,Okt,Nov,Des)
    			VALUES(@CustomerID,@Jan,@Feb,@Mar,@Apr,@Mei,@Jun,@Jul,@Agt,@Sep,@Okt,@Nov,@Des);
                             
                              [B]-- Set @@Error to variable if an error occure in second query
    		SET @Err2 = @@ERROR
    
    		-- Check if an error when inserting data then doing ROLLBACK and if no error then COMMIT
    		IF @Err1 = 0 AND @Err2 = 0
    			BEGIN
    				COMMIT TRANSACTION
    				PRINT 'SUCCESS'
    			END
    		ELSE
    			BEGIN
    				ROLLBACK TRANSACTION
    				
    				SELECT @CustomerID = CustomerID,
    					@Modifier = Modifier
    				FROM Customer
    				WHERE (CustomerID = @CustomerID);
    				
    				PRINT 'FAILED!' + LTRIM(STR(@@ERROR))
    			END
    END
    hope this usefull.
    Last edited by witecloner; 05-14-09 at 10:44.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2user, Something else you, (or anyone else reading this thread) could consider is BEGIN ATOMIC. The following is not a working example but gives an idea of how it works

    Create Procedure...

    Begin Atomic

    INSERT 1;
    INSERT 2;
    INSERT 3;

    END

    More needs to be put around this (so you know which statement failed) but what ATOMIC gets you is that ALL statements have to complete successfully or all statements are rolled back.

    Per you specifications, if INSERT 1 works but INSERT 2 fails, INSERT 1 is rolled back.
    If INSERT 1 and INSERT 2 work but INSERT 3 fails, INSERT 1 and 2 are rolled back.

    You still need the handlers but this could be something else to look into.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •