Sql-server – Trigger Logic (from SQL 6.5 )

sql serversql-server-2008-r2trigger

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.

Best Answer

I'm not sure if this code is fully accurate for columns that have more than 1 row with the same value, but I suppose that's a different subject. What I see is that the only value the null counter is ever initialized to is 0. That means it will never have an effect on the addition. Putting on my dev hat, I could see the start of an unfinished concept.

If I needed to update a column to NULL, the where clause on line 9 wouldn't work. The test NULL = NULL is false. That means the join logic fails for update values of null. That being said, it appears the error handling for that was never implemented beyond having a count column to use.

Related Question