Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sa4720k
Starting Member
3 Posts |
 Posted - 2006-04-11 : 09:19:25
|
| Greetings,I'm trying to merge two databases. Specifically, a table within two databases. This table has a one-to-many relationship with another table in the database so basically I will be merging two tables into two other tables. I'm trying to accomplish this by using an INSERT trigger on one of the tables (the main table) that is being updated with the new information using DTS.My plan is to find the PK (the identity column) value from the first table for each row as it is being inserted into the second table, use that PK to select the matching records in the linked table and insert them in the corresponding table in the second database. (I hope this makes sense)My question is: is there a way to find the PK (not @@IDENTITY) of the row as it is being inserted each time? Is what I'm thinking of even possible or should I be thinking of another way to do this?Any help is greatly appreciated.Thanks. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-11 : 12:57:03
|
| You should NOT use triggers to implement cross-database business rules. This is not what they are for. The scope of triggers should be limited to the table at best, and the database at worst.There are other tools for synchronizing tables, from the low-tech staging table/sql agent job to the high-tech replication agents.But triggers? They should not reference resources outside of their scope. |
 |
|
|
|
|
|
Database merge with Triggers