Post reply

Automatically copy data into another database upon insert - triggers?

  • October 20, 2008 at 7:47 am

    #71373

    Hello all,

    I posted this in the T-SQL forum on the basis of the "Triggers" but I guess it is more of an administration issue...

    I have been asked to set up an interface which will automatically populate one database when a record is created in another database.

    I have used triggers to do this before, when it has involved a single table populating to a single table, but this one is a biut more complicated.

    Basically I have one SQL Server 2000 database:

    "Source1" which is an HR database and is used by the HR department on a day to day basis. All new employees are manually entered via a client which creates entries on 3 separate tables when the transaction is committed. (EmployeeMaster, EmployeePayroll and EmployeePosition)

    There is another server containing another SQL Server 2000 Database - "Destination1" which historically has always been used for employee data and everything was always created manually here too.

    *Now they want to avoid a 2 step entry on these systems.

    The problem is that the 3 source tables contain a mix of data which needs to be populated on another 2 tables (Empltable, HRMVirtualNetworkTable) and some fields need to be taken from each of the 3 source tables in order to create a record on the destination tables.

    Using triggers, I cannot think of a way in which to capture all of the relevant information into one single record. By just populating the destination tables directly, I will get 3 partial records created from each trigger, since an insert is occurring on each individual table, and they each have their own associated "Inserted" temporary table.

    What I would basically like to know is:

    Is it possible to manage a job like this via Triggers, where the data required is stretched across multiple tables?

    Is there another easier method which can be used to automatically populate information into some tables when entries are created on another table?

    I did think of using an Agent job which just pulls all of the relevant information on a regular timescale, which makes more sense but would appreciate some advice and if anyone else has encountered a similar problem what sort of solution have you come up with?

    There is not timestamp column on any of the required tables in the source database, so if I did have to go down the route of using an Agent Job, I'm not sure how to filter down the records to only replicate newly created or updated records....

    Any suggestions would be much appreciated.

    Thanks in advance for any replies 🙂

  • October 20, 2008 at 7:58 am

    #887129

    Here's one way to do this:

    In the source database or on the source server create a transfer/queue table or tables with the employee primary key, action(insert/update/delete) and a processed date or flag that you populate using insert/update/delete trigger(s). Then using a job/windows service/dts package, run a query that retrieves the relevant employee data and inserts/updates/deletes on the destination and, upon successful transfer, mark the row as processed.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 20, 2008 at 8:14 am

    #887138

    Many thanks Jack,

    This would still leave me with either;

    3 separate tables being populated by triggers at the source end (with no direct relation to the destination tables)

    1 table with fragmented records populated by 3 separate table triggers

    You've set me in the right direction though. I guess if, like you say, I copied the data to separate tables with a trigger, I could then query the 3 tables to pull together the data I need and copy to the destination as well as an archive table. The holding tables can then be cleared upon completion.

    Is there any way around the triggers firing off separately though(i.e. to combine the fields of the 3 tables into a single record?) I'll give this method a try anyway so thanks a lot for that 🙂

  • October 20, 2008 at 8:36 am

    #887148

    I think you need all 3 triggers because I would assume it is possible to update just 1 of the tables. You could put a check in your triggers that does not insert a new row if there is an existing, non-processed row.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 20, 2008 at 10:15 am

    #887216

    I did something similar years ago, but I used replication. Basically when something changed in source, I replicated the relevant table to the destination to staging tables. 3 (or however many) small tables that exist in your destination and then you can transform the data to the destination database.

    This does a couple things.

    First it decouples the databases. If you need to move one, take it offline, restore it, etc., you don't have triggers breaking on the source side.

    Second, replication picks things up in a smoother fashion. Plus it's easy to move to another server if one of the databases needs to move.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 5 posts - 1 through 4 (of 4 total)

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