I've been looking at the murky world of SQL Server 2000 Update triggers recently, and none of it's as straightforward as I'd imagined. There seems to be unnecessary confusion around the columns_updated() function, which is all explained brilliantly in this article. I realised where I was going wrong with my user update trigger - because the user table had so many columns, the standard "catch-all" function to see if anything had changed, only referenced the first eight columns of the table. This all runs at single bit level, so 8 pieces of data is all that will be calculated in a single reference to the columns_updated() function. Therefore this is fine for a database table with up to eight columns:
IF (substring(columns_updated(), 1, 1)) > 0
-- Do stuff...
However I needed four of these statements to include all the user columns:
IF (substring(columns_updated(), 1, 1)) > 0 OR (substring(columns_updated(), 2, 1)) > 0 OR (substring(columns_updated(), 3, 1)) > 0 OR (substring(columns_updated(), 4, 1)) > 0
-- Something has changed, therefore update the record...
Rather laborious, but preferable to checking each column's status separately. Do read the article, it's quite excellent, hats off to Andy Warren.
No comments:
Post a Comment