question

David 2 1 avatar image
David 2 1 asked

Transaction Processing In SQL Server 2000

What's the best way to script a transaction so that it will either rollback or commit depending on the count of the rows requiring updated equaling the actual rows updated? So far I have coded the below (with example test table and data) which appears to work however when a transaction is rolledback because the count doesn't equal the update both the 'Transaction rolledback' and the 'Transaction committed' messages are both printed to screen. I only want the one appropriate message displayed as required.

CREATE TABLE test(                    
col1 VARCHAR(5),                    
col2 VARCHAR(5))                        
                    
INSERT INTO test VALUES('1','A')                    
INSERT INTO test VALUES('2','A')                    
INSERT INTO test VALUES('3','C')                    
INSERT INTO test VALUES('4','D')                      
                    
DECLARE @row INT                      
SELECT @row = (SELECT COUNT(col2)                        
           FROM test                       
           WHERE col2 = 'A')                      
BEGIN TRANSACTION                       
           UPDATE test                      
           SET col2 = 'AA'                      
           WHERE col2 = 'A'                      
IF (SELECT COUNT(col2)                      
    FROM test                      
    WHERE col2='AA') <> @row                      
BEGIN                       
    ROLLBACK TRANSACTION                      
    PRINT 'Transaction rolledback'                    
END                    
ELSE                       
    COMMIT TRANSACTION                      
    PRINT 'Tranaction committed'                      

Thanks in advance.

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel 1 avatar image
Squirrel 1 answered
BEGIN               
    ROLLBACK TRANSACTION              
    PRINT 'Transaction rolledback'            
END            
ELSE               
BEGIN            
    COMMIT TRANSACTION              
    PRINT 'Tranaction committed'              
END            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.