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.