I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will contain extra columns in database B compared to A.
For some of the tables in Database B, Whenever its corresponding table in Database A is changed, the table in B needs to be updated immediately to reflect that change. In the case of extra columns in the Database B table, I might need to write additional logic to populate them.
If Database B cannot be updated, I want the update to Database A to fail too.
How could I set up such a scheme? Can I set up a trigger to automatically send the same update/insert/delete statement to Database B? Is there another way?
BTW, Database A = Sql server 2000, Database B = Sql server 2008.
Thanks in advance!
Background: There is a huge database of ~500 tables. There are many frontends that use this big database. I'm replacing the frontends one at a time. To prove that it integrates with all the legacy frontends I need to run a second system in parallel with their production system. To keep double data entry to a minimum I want to copy table modifications from the production system for all tables that aren't updated by my replacement frontend.