Columns_updated() in a trigger started giving incorrect column after a column has been removed form the table.

• girish.nehte-1109699

  SSC Veteran

  Points: 288

  More actions

  March 31, 2014 at 2:15 am

  #306026

  Hi All,

  I am using SQL Server 2008 for my project. in one of my table I have written an update trigger. In that trigger I have used Columns_updated() function to know which column has been updated. In that table I have some 190 columns. I am using substring of Columns_updated() to get the correct column updated. It was working fine.

  But now before sometime I have removed a column from my database, and after that this method started giving me incorrect column updated.

  What I can do to correct this behavior so that Columns_updated() method will again start giving me the correct column name?

  Please let me know if anymore info is required. I need this answer very badly.

  Regards,

  Girish

• Lowell

  SSC Guru

  Points: 323504

  More actions

  March 31, 2014 at 6:52 am

  #1701560

  you have to alter your trigger. your staticly written trigger did not change to now match the new results from columns_updated() function

  columns updated uses a map to say which columns were used in the update. if you drop a column, the column is not represented in the map, and your existing logic, which might have expected changes in columns 2-8, now needs to be tweaked.

  a coded example of columns_updated:

  CREATE TABLE MYADDRESSES(

  EMPNO INT IDENTITY(1,1) PRIMARY KEY,

  ENAME VARCHAR(100),

  ADDR1 VARCHAR(100),

  ADDR2 VARCHAR(100),

  CITY VARCHAR(100),

  STATECODE VARCHAR(2),

  ZIPCODE VARCHAR(100),

  PHONE VARCHAR(20),

  MOREDATA VARCHAR(100)) --column 9 will not track changes

  CREATE TABLE ADDRESSCHANGES(

  EMPNO INT ,

  COLCHANGEDBITMASK INT, --example: saved so you can audit which cols actually changed, but they are both here anyway!

  OLDENAME VARCHAR(100),

  OLDADDR1 VARCHAR(100),

  OLDADDR2 VARCHAR(100),

  OLDCITY VARCHAR(100),

  OLDSTATECODE VARCHAR(2),

  OLDZIPCODE VARCHAR(100),

  OLDPHONE VARCHAR(20),

  NEWENAME VARCHAR(100),

  NEWADDR1 VARCHAR(100),

  NEWADDR2 VARCHAR(100),

  NEWCITY VARCHAR(100),

  NEWSTATECODE VARCHAR(2),

  NEWZIPCODE VARCHAR(100),

  NEWPHONE VARCHAR(20))

  GO

  --modified from http://www.sqlservercentral.com/Forums/Topic593727-148-1.aspx#bm595054

  CREATE TRIGGER TR_MYADDRESSES

  ON MYADDRESSES

  AFTER UPDATE AS

  /*Check whether columns 2 thru 8 have been updated. If any or all

  columns 2, 3 or 4 have been changed, create an audit record. The

  bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test

  whether all columns 2, 3, and 4 are updated, use = 14 instead of > 0

  (below).

  this one specifically is for 32 thru 8 is:

  select power(2,(3-1))

  + power(2,(4-1))

  + power(2,(5-1))

  + power(2,(6-1))

  + power(2,(7-1))

  + power(2,(8-1)) = 252*/

  --IF (COLUMNS_UPDATED()) <> 0

  IF (substring(COLUMNS_UPDATED(),1,1) & 252 )>0

  BEGIN

  --inside a trigger, two special tables exist for the duration of the trigger:

  --the table INSERTED and the table DELETED

  --an UPDATE would have data in both tables...the value WAS DELETED to be replaced with the value from INSERTED

  INSERT INTO ADDRESSCHANGES(EMPNO, COLCHANGEDBITMASK, OLDENAME, OLDADDR1, OLDADDR2, OLDCITY, OLDSTATECODE, OLDZIPCODE, OLDPHONE, NEWENAME, NEWADDR1, NEWADDR2, NEWCITY, NEWSTATECODE, NEWZIPCODE, NEWPHONE)

  SELECT

  DELETED.EMPNO,

  COLUMNS_UPDATED(),

  DELETED.ENAME,

  DELETED.ADDR1,

  DELETED.ADDR2,

  DELETED.CITY,

  DELETED.STATECODE,

  DELETED.ZIPCODE,

  DELETED.PHONE,

  INSERTED.ENAME,

  INSERTED.ADDR1,

  INSERTED.ADDR2,

  INSERTED.CITY,

  INSERTED.STATECODE,

  INSERTED.ZIPCODE,

  INSERTED.PHONE

  FROM DELETED

  INNER JOIN INSERTED

  ON DELETED.EMPNO = DELETED.EMPNO

  END;

  GO

  --insert some test data

  INSERT INTO MYADDRESSES( ENAME, ADDR1, ADDR2, CITY, STATECODE, ZIPCODE, PHONE, MOREDATA)

  SELECT 'Kalvin','123 My Imagination St','','Miami','FL','33024','555-1212','likes snowmen'

  UNION

  SELECT 'Hobbes','123 My Imagination St','','Miami','FL','33024','555-1222','likes to tease calvin'

  --renaming Calvin doesn't raise the trigger

  UPDATE MYADDRESSES SET ENAME='Calvin' where ENAME='Kalvin'

  --changing the Address DOES:

  UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' where ENAME='Calvin'

  --changing multiple rows at the same time does trigger

  UPDATE MYADDRESSES SET STATECODE='NY'

  --setting the value to the same value repeating a previous insert:

  UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' where ENAME='Calvin'

  select * from MYADDRESSES

  select * from ADDRESSCHANGES

  drop trigger TR_MYADDRESSES

  drop table MYADDRESSES

  drop table ADDRESSCHANGES

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!