I have a database that was originally implemented in SQL 6.5 and has been migrated to SQL2000, SQL2005 and now SQL2008R2 (Compatibility Mode = 10). Through the migration many new tables, functions, and procedures have been added and many of the newer T-SQL features are in use (e.g., Merge, Windowing Functions, Try-Catch, Table Variables, ...)
However, most of the underlying Referential integrity is still implemented in Triggers. :(
I have run across a T-SQL Code pattern that puzzles me. I knew some of the original implementers (from circa 1995) and they were not dummies but the reason for this pattern escapes me. Any suggestions will be appreciated. This example is from an After Insert Trigger but it is representative.
Declare @validCnt int , @nullCnt int , @rowCnt int Select @rowCnt = @@rowCount If update (mycol) Begin Select @validCnt = count(*) From inserted, myLookupTable Where inserted.myCol = myLookupTable.myCol Select @nullCnt = 0 If @validcnt + @nullCnt != @rowCnt Begin RaisError() End End
I understand what the code is trying to accomplish but I don't understand the reason for @nullCnt. I hesitate to refactor the code for fear that there is some hidden purpose for the code as is.
My inclination is to change the test to read something like
If Update(myCol) and @rowCnt != (Select count(*) From ... Where...) RaisError()
Any thoughts or suggestions? BTW, we are talking about ~200 triggers total. Thanks Ray
Why would I refactor working code? Well, as I understand the term you would only refactor working code. If it weren't working you would be debugging or fixing. :) Isn't the purpose of refactoring is to make the working code "better" in some sense? Efficiency, readability, reliability, ... However, the code is not working if we plan to migrate to some more current version of SQL (like even 2012). It seems the RaisError syntax in the trigger generates a run-time error in 2012 and later. SQLServer lets me restore the database and change the compat mode to 2012 or 2014 but when a trigger is executed there are a variety of runtime issues. Also, most of the triggers are implementing actions that could be performed directly in the T-SQL or by Constraints/Foreign Keys. For example, the sample I posted just checks to see if I actually inserted the correct number of rows.