Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Trigger on any column update

    I have 2 tables (table1 and table2) - these are the same tables (table 2 is a backup of table 1)
    I want that every time a row gets updated or a new row entered to trigger an event that will update table 2
    As far5 as the table, any column(s) can be update and there are 50+ columns

    The problem i am having when doing the below trigger is that when an update or insert is done on table1, table1 gets appended to table2 which then has duplicates.

    I thought this was right - (substring(columns_updated(),1,1)) > 0 - to check if a column has been changed within a row

    Can you please modify the trigger or If there is any other way??


    CREATE TRIGGER trig_test
    ON table1
    FOR INSERT,UPDATE
    AS
    INSERT INTO table2
    SELECT * FROM table1 where (substring(columns_updated(),1,1)) > 0
    GO


    Thanks
    Beyond Limitation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have a primary key, I hope? Good. Then how about:

    CREATE TRIGGER trig_test
    ON table1
    FOR INSERT,UPDATE
    AS
    Delete from table2 inner join inserted on table2.PrimaryKey = inserted.PrimaryKey
    insert into table 2 select * from inserted
    GO

    blindman

  3. #3
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    whoops I forgot to mention that--
    no primary keys

    all the tables used are import form a dbf IV file
    when it imports, it sets all data types to varchar and it does not set any primary keys (the only thing it keeps is the column names and size from the dbf file)

    but there is a column that is unique by account numbers ( but not set to unique) (and would be to difficult to set it to unique due to an out sourced programmer creates the account numbers which means i can't modify the code nor tables (i know bad idea)

    but anyways instead of primarykey i can use accountno ?

    Delete from table2 inner join inserted on table2.Accountno = inserted.Accountno

    thnx

    PS using your way i get error "Incorrect syntax near the keyword 'inner'.
    " Playing around with the code now.
    Last edited by vextout; 10-13-03 at 16:08.
    Beyond Limitation

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, use can use AccountNO as long as it is a unique key, even if it isn't defined as such. It is NOT good practice, so be careful.

    Sorry about the syntax error.

    CREATE TRIGGER trig_test
    ON table1
    FOR INSERT,UPDATE
    AS
    Delete table2
    from table2
    inner join inserted on table2.accountno = inserted.accountno
    insert into table2 select * from inserted
    GO

    No, this is not good programming practice. Make frequent backups. I'm thinking you will need them eventually...

    blindman

  5. #5
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thnx blindman

    just curious - how much resource would it take up to do this trigger while getting about 10 transactions every minute ?

    would this be efficient way to do this ?
    Would it slow the sql server down significantly?

    Ps - i can't believe i also overlooked the "delete table2 from ..."

    Thanks for all the help
    Beyond Limitation

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It would be faster if you had an index, but unless your table is truly gigantic it should be able to take 10, 20, or even hundreds of updates without any problems.

    But now you've got my curiosity going. Why in the world do you NEED to keep two tables exactly in synch with eachother? Have you looked into replication?

    blindman

  7. #7
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    i thought replication was not used in real-time transactions

    i thought it was more a manual / timely-based replicating event

    ???????
    Beyond Limitation

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. There are several typess of replication. Transactional Replication can use Log Shipping to achieve very low latency.

    blindman

  9. #9
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thats good to know. - i looked around for afew secodns and came across an article in Database Journal

    showing where i cna choose transactional replication (screenshots)

    I attempted rep before but - i guess over looked it

    now i gotta go to work and try to set it up.


    Thanks for all the help
    Beyond Limitation

Posting Permissions

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