March 31, 2014 at 2:15 am
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
March 31, 2014 at 6:52 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply