SQLServerCentral

Logic for traversing thru different columns in one row


https://www.sqlservercentral.com/Forums/Topic593727.aspx

By sql_learner29 - Wednesday, October 29, 2008 2:49 AM

Hi all,


I have a requirement to loop through a row for different columns, Can anyone give me some inputs for how can I do this in SSIS.

The question is like this:

I am tracking changes to certain columns, now I want to keep record in a fact table ,for changed columns in one row ...

for ex:

If I have these columns from my source:-

empno ename address city state phone


now the columns which are critical and i need to maintain changes are only address, city , state
so for any change in address create a record in fact with changes done ..
but the question here is suppose if 2 critical attributes changes at the same time and come to me together...now it will create record for first one that it looked up and will not lookup again to the same row for other changed column...but i want to loop and lookup again for other critical attributes that may have changed...

Actually, I am not getting any logic for this Can any one provide me sum help on this?



Thanks in Advance
Ruchika
By sql_learner29 - Thursday, October 30, 2008 1:17 AM

any help plzzzzzz
By Lowell - Thursday, October 30, 2008 1:25 AM

you could easily do this automatically in a trigger, especially by using the new COLUMNS_UPDATED() function that exists for triggers in SQL2005; if a change occurs, you could have a trigger automatically log the changes to an audit table...
Are you sure you need to do this in SSIS, or just 'get er done'?
By Lowell - Friday, October 31, 2008 3:03 AM

ok here's an example for you to play with to understand the concept:
create the two tables, and select from the two tables after every step;
this example says if columns 3,4,5,6,7 or 8 are modified, logit to an audit table; changing column 1,2 or 9 does not cause an audit.

updating a value to the same value still triggers an audit..so if you set ADDR1 = to the same value it had previously, it still fires the trigger.


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 3 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) &amp; 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



By sql_learner29 - Friday, October 31, 2008 3:05 AM

Thanks man for the prompt response...let me try dis...
By sql_learner29 - Friday, October 31, 2008 1:57 AM

Thanks......I don't know how trigger will help me to track every column...

will it insert a new row for every column changed in a row?

I mena if address and location both changes, will it give me two rows in the transaction table?

Thanks
By sql_learner29 - Friday, October 31, 2008 7:16 AM

Triggers been expensive...Can anyone tell me any other way to do this..I got from some forum that we can use CHECKSUM function or CHECKSUM transformation for this...

Have anyone got any idea abt how to use the checksum TSQL or transformation?

I never used it so have no idea at all....
Thanks