davej Posted May 5, 2011 Share Posted May 5, 2011 What is the best way to keep track of what has happened inside a trigger or procedure before executing a COMMIT ? I am thinking in terms of SQL Server. Thanks. Link to comment Share on other sites More sharing options...
davej Posted May 9, 2011 Author Share Posted May 9, 2011 The experimenting I have done so far seems to indicate that an approach like this;BEGIN TRANSACTIONDECLARE @ROWCNT INTEGERDECLARE @ERRCNT INTEGER...UPDATE EMPLOYEESET Phone = '123-456-7890'WHERE EmpID = 12345;SET @ROWCNT = @ROWCNT + @@ROWCOUNTSET @ERRCNT = @ERRCNT + @@ERROR...Does not work in SQL Server 2008. The second SET statement is affected by the first SET statement.What I am using at the moment is a summing of @@ROWCOUNT inside a TRY-CATCH. That seems to work fairly well. Link to comment Share on other sites More sharing options...
packrat Posted May 10, 2011 Share Posted May 10, 2011 not exactly sure what you're trying to keep track of, but given the example above... SELECT @ROWCNT = @ROWCNT + @@ROWCOUNT ,@ERRCNT = @ERRCNT + @@ERROR ... would preserve the @@ERROR while incrementing the @ROWCNTIf you want a count of errors you might need to alter that a little as @@ERROR will have a non 0 error code if an error occured and it appears that you're looking for an error count SELECT @ROWCNT = @ROWCNT + @@ROWCOUNT ,@ERRCNT = @ERRCNT + CASE WHEN @@ERROR != 0 THEN 1 ELSE 0 END will give you an error count rather than summing the error codes.happy coding Link to comment Share on other sites More sharing options...
davej Posted May 10, 2011 Author Share Posted May 10, 2011 not exactly sure what you're trying to keep track of, but given the example above...SELECT @ROWCNT = @ROWCNT + @@ROWCOUNT ,@ERRCNT = @ERRCNT + @@ERROR ... would preserve the @@ERROR while incrementing the @ROWCNTIf you want a count of errors you might need to alter that a little as @@ERROR will have a non 0 error code if an error occured and it appears that you're looking for an error count SELECT @ROWCNT = @ROWCNT + @@ROWCOUNT ,@ERRCNT = @ERRCNT + CASE WHEN @@ERROR != 0 THEN 1 ELSE 0 END will give you an error count rather than summing the error codes.happy coding Ok, that looks good -- so I put them on one line with a comma. I have never used CASE in SQL. I am just trying to verify that the expected number of rows were indeed affected and that there were no errors before issuing COMMIT. Do you know if I can throw an error to test this? Thanks. Link to comment Share on other sites More sharing options...
packrat Posted May 10, 2011 Share Posted May 10, 2011 you can throw a test error with: RAISERROR 'test error' CASE is very powerful, I highly recommend becoming familiar with it.if you're outside a try/catch block there's a good chance that any significant error is going to exit your routine, which if you're using explicit transactions can be catastrophicI can't emphasize enough the importance of reading the books on-line topics regarding transactions, try/catch and error handling as well as running some unit tests to see how these piece fit together in order to get a feel for them in practice before using them in a production environment.if you goto books on line and find TRY...CATCH in the Transact SQL reference; examples B & C both illustrate working with transactions in try/catch blocksalso check out the topic "Database Engine Error Severities", some severity levels will only return warnings and not throw an exception in a try/catch, others will trip a try/catch but not pop out of a routine otherwise, some will sever the current connection, the highest severities indicate hardware or software problemsif you have any reservations or confusion about using transactions you're probably better off not using a trans than getting one wrong and ending up with deadlocks... remember that there's always an implicit transaction, if a data modification statement fails nothing is changed... Link to comment Share on other sites More sharing options...
davej Posted May 11, 2011 Author Share Posted May 11, 2011 For now I am using something like this... DECLARE @RCNT INTEGERBEGIN TRANSACTIONBEGIN TRYUPDATE TABLE1SET NAME = @nameWHERE EmpID = @EmpIDSET @RCNT = @@ROWCOUNT -- should be 1UPDATE TABLE2SET DEPT = @deptnameWHERE EmpID = @EmpIDSET @RCNT = @RCNT + @@ROWCOUNT -- should be 2UPDATE TABLE3SET MANAGER = @supernameWHERE EmpID = @EmpIDSET @RCNT = @RCNT + @@ROWCOUNT -- should be 3IF (@RCNT = 3)BEGIN COMMIT TRANSACTION PRINT 'SUCCESS'ENDELSEBEGIN ROLLBACK TRANSACTION PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT' ENDEND TRYBEGIN CATCH ROLLBACK TRANSACTION PRINT 'DATABASE ERROR OCCURRED' END CATCH Link to comment Share on other sites More sharing options...
packrat Posted May 11, 2011 Share Posted May 11, 2011 looks good. tried testing by raising some errors in there and also by passing bad data?might be able to simplify, if you know you want to bail out if any of those statements fail to affect a record you can omit the count and the following statements by raising your own exceptionI didn't test this, so I might have a syntax error in here, bear with me --DECLARE @RCNT INTEGERBEGIN TRANSACTIONBEGIN TRYUPDATE TABLE1SET NAME = @nameWHERE EmpID = @EmpID--SET @RCNT = @@ROWCOUNT -- should be 1IF @@ROWCOUNT != 1 RAISERROR 'UNEXPECTED ROWCOUNT'UPDATE TABLE2SET DEPT = @deptnameWHERE EmpID = @EmpID--SET @RCNT = @RCNT + @@ROWCOUNT -- should be 2IF @@ROWCOUNT != 1 RAISERROR 'UNEXPECTED ROWCOUNT'UPDATE TABLE3SET MANAGER = @supernameWHERE EmpID = @EmpID--SET @RCNT = @RCNT + @@ROWCOUNT -- should be 3IF @@ROWCOUNT != 1 RAISERROR 'UNEXPECTED ROWCOUNT'/*IF (@RCNT = 3)BEGIN COMMIT TRANSACTION PRINT 'SUCCESS'ENDELSEBEGIN ROLLBACK TRANSACTION PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT' END*/END TRYBEGIN CATCH ROLLBACK TRANSACTION IF ERROR_NUMBER() = 50000 PRINT 'ERROR OCCURRED -- UNEXPECTED ROWCOUNT'; ELSE PRINT 'DATABASE ERROR OCCURRED'; END CATCH Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.