DTS packages and Triggers

  • This is sort of expanding on a recent query that has been answered, but my problem is a little different.

    I have a table that is updated on a regular basis (on server 'A'). Another server (server 'B') then runs a DTS package which generates 7 other tables from the table on server 'A'. The DTS package runs every hour, on the hour - we are only talking 5,000 records.

    However, what I would like to do, is have a trigger on the table on server 'A' that would run everytime a record is insert, updated or deleted, that executed the DTS package on server 'B'.

    My question is: Would the trigger on server 'A' be executed for EVERY record that changed, bearing in mind that the entire table (on server 'A' is dropped and re-populated) when it gets updated?

    I've yet to write the trigger as it will be one of the first ones I've written, so any advice you can give would be greatly appreciated.

    I forgot to mention - the table on server 'A' also has a trigger that updates another table on server 'A' with the last time that it was re-populated.

    Edited by - pinhead on 09/10/2003 09:59:31 AM

  • When you have an INSERT trigger on table A, it deals with as many rows as many were inserted into your table A by a single INSERT statement. All those rows are found in the table called [inserted] inside the trigger. If you need to perform an operation for eacbh row from that set, you need to take that in consideration.

    Generally, it is NOT a good idea to run something like a DTS package from a trigger. If you have stored procedures ONLY interface to your tables (the best practice), then you could call the DTS package from that procedure.

  • Have you given any thought to Transactional Replication?

Viewing 3 posts - 1 through 2 (of 2 total)

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