Logic for traversing thru different columns in one row

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    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

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    any help plzzzzzz

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Lowell

    SSC Guru

    Points: 323309

    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’?

    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!

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    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

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Lowell

    SSC Guru

    Points: 323309

    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.

    [font=”Courier New”]

    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[/font]

    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!

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    Thanks man for the prompt response…let me try dis…

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    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

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply