Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: cursor question

  1. #1
    Join Date
    Mar 2004
    Posts
    25

    Angry Unanswered: cursor question

    hi

    From general point of view I would like to update table 'warehouse' everday from another table 'table dump' and to checkif some of the columns have changed in table 'dump' against old values in table 'warehouse'. If there is some change I need to backup
    old values in some kind of history_logs table.

    So I begun by writing a stored procedure for this...

    DECLARE ticket_dump_cursor CURSOR FOR
    SELECT * FROM ticket_dump order by Ticket_ID

    OPEN ticket_dump_cursor

    FETCH NEXT FROM ticket_dump_cursor

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- insert code later


    END
    CLOSE ticket_dump_cursor
    DEALLOCATE ticket_dump_cursor

    GO

    Now the question? is there a way how to store one row as some kind of
    row datatype (something like in POSTGRE SLQ is record datatype) and then
    iterate trough columns and check their value against old values in 'warehouse' table

    I don't want to use FETCH NEXT FROM dump INTO @var1, @var2
    because I need to check about 50 columns..

    How to trick this? Desperately waiting for your suggestions..

    thanks a lot

    miso

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: cursor question

    I don't know how to accomplish this in SP, but if a client app is possible, I would use DAO or better ADO technology, which does enable you to loop through all fields of a record. I can provide you with some example code.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Mar 2004
    Posts
    25

    possible solution...

    Hi

    thanx for suggestion but I would like to us dynamic SQL if possible...
    I am not very familiar with ADO and to tell the truth I would like to avoid it if I can...If it is the only way then please send me examples you mentioned

    e-mail michal.holecka@accenture.com

    I was thinking about alterative solution and maybe I could do the job this way:

    I would simply update rows in 'warehouse' table and write trigger which would fire after update. This trigger will check if some column has changed and if it has been changed it will log the old value into history table..

    The only problem is that inside this trigger I will have to check all 50 columns manualy ... that's not very systematic solution.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You can archive the entire row if you want through a trigger with the type of action that was performed , that is Insert , Update Or delete
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Mar 2004
    Posts
    25
    yeah I can do that but there will be a lot of redundancy in storing the whole row..

    imagine that this row has 56 columns exactly and only one column will change.. I will store the whole row. Then another row changes. and so on..

    I was thinking about some kind of history logging that works like this..
    if one column will change I will find out which one and log only column name and old value into history table not the whole row.

    Does it make sense to you?

    Can I do it somehow this way?

    thank you for your time ..

    michal holecka

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Its a choice between space and performance ...

    Trying to figure out which column has changed would take about 56 comparisons ....

    Hmm ... ask your managers to get a bigger disk
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Your original idea was to compare your table with a copy of the day before?! It can be done, but you would have to compare keys to detect inserted and deleted rows. To detect field changes, you would have to join your current and copied table on your key, and to compare field values.

    A complete other approach is event-driven to catch every database change. This approach is mostly used in DWH environments to trace db changes.

    The question is thus: what do you want to accomplish?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by mikosan
    I was thinking about some kind of history logging that works like this..
    if one column will change I will find out which one and log only column name and old value into history table not the whole row.

    Does it make sense to you?

    Can I do it somehow this way?
    You can detect, which columns are updated. See the CREATE TRIGGER syntax:

    CREATE TRIGGER trigger_name
    ON table
    [WITH ENCRYPTION]
    {
    {FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
    [WITH APPEND]
    [NOT FOR REPLICATION]
    AS
    sql_statement [...n]
    }
    |
    {FOR { [INSERT] [,] [UPDATE] }
    [WITH APPEND]
    [NOT FOR REPLICATION]
    AS
    { IF UPDATE (column)
    [{AND | OR} UPDATE (column)]
    [...n]
    | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
    { comparison_operator} column_bitmask [...n]
    }
    sql_statement [ ...n]
    }
    }


    Example:

    CREATE TRIGGER updEmployeeData
    ON employeeData
    FOR update AS

    -- Check whether columns 2, 3 or 4 has been updated. If any or all of
    -- 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 check if all columns 2, 3, and 4 are updated, use = 14 in place of
    -- >0 (below).

    IF (COLUMNS_UPDATED() & 14) > 0
    -- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
    -- columns 2, 3, and 4 are updated.
    BEGIN
    -- Audit OLD record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    .....
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Mar 2004
    Posts
    25

    I want to do following..

    sorry if I mady myself unclear.

    I have some identifier and according this value I can tell whether the row will be updated or appended to dwh table.

    For those rows that will be updated I will try backup old values.. problem is that we are talking about 56 columns.

    My idea was not to backup the whole row but to have some mechanism like somehow iterate through columns of one row and log to history table following

    changed_column_name name
    old_value

    If I understand it well I am able to do that but only with 56 IF UPDATES...

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by DoktorBlue
    You can detect, which columns are updated. See the CREATE TRIGGER syntax:

    CREATE TRIGGER trigger_name
    ON table
    [WITH ENCRYPTION]
    {
    {FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
    [WITH APPEND]
    [NOT FOR REPLICATION]
    AS
    sql_statement [...n]
    }
    |
    {FOR { [INSERT] [,] [UPDATE] }
    [WITH APPEND]
    [NOT FOR REPLICATION]
    AS
    { IF UPDATE (column)
    [{AND | OR} UPDATE (column)]
    [...n]
    | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
    { comparison_operator} column_bitmask [...n]
    }
    sql_statement [ ...n]
    }
    }


    Example:

    CREATE TRIGGER updEmployeeData
    ON employeeData
    FOR update AS

    -- Check whether columns 2, 3 or 4 has been updated. If any or all of
    -- 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 check if all columns 2, 3, and 4 are updated, use = 14 in place of
    -- >0 (below).

    IF (COLUMNS_UPDATED() & 14) > 0
    -- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
    -- columns 2, 3, and 4 are updated.
    BEGIN
    -- Audit OLD record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    .....

    This approach will work only till you dont make any changes to your table schema . If for ex. I remove the first column , what will happen
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Mar 2004
    Posts
    25
    If I use your way
    IF (COLUMNS_UPDATED() & 14) > 0
    -- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of
    -- columns 2, 3, and 4 are updated.
    BEGIN
    -- Audit OLD record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    .....


    Can I do something like
    while

    If (COLUMNS_UPDATED() &

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by Enigma
    This approach will work only till you dont make any changes to your table schema . If for ex. I remove the first column , what will happen
    I assumed a fixed structure, indeed. Mikrosan, correct me if this isn't the case.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Mar 2004
    Posts
    25
    yes it will be static structure it won't change...

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    But why still go for the column checks .... is disk space really that expensive ??? I would anytime sacrifice disk space for performance
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Mar 2004
    Posts
    25
    To be really honest I am just junior programmer and seniors advised/ordered me to do that this way...

    But considering your replies I see that I will have to talk to him..


    thank you guys you are opening my eyes all the time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •