Referential Integrity between Databases

[jcg6]

I was wondering if it was possible to use referential integrity to relate tables between databases? I have two database that use a similar table. If the value is updated in the primary table (first database) I want those records to update in the secondary table (second database). Is this possible, and if so - how? Thanks for any help that you can offer.

 

[JamesLean]

Using a trigger would solve your problem:

CREATE TRIGGER tr_tableA_upd
  ON tableA
  FOR UPDATE
AS

UPDATE b
SET keycol = i.keycol
FROM db2..tableB b JOIN deleted d ON b.keycol = d.keycol
  JOIN inserted i ON d.keycol = i.keycol
GO

--James

 

[jcg6]

Sorry that it took me so long to get back to your response. I have been out of the office and involved in a huge project. Now that I have the time to focus on my databse, I have a few quick questions about your suggestion?

I have never used triggers before, so the syntax is new to me. I will list my table names and columns. If you could plug them into your trigger's syntax that would be very helpful to me. Thanks so much for your help, I really appreciate it.

Primary database - HardwareDB (this is where the updates occur)
Table - Hardware
PK Column - MachineID

Secondary database - SoftwareDB
Table - LicensedPCs
PK Column - TransactionID
FK Column - MachineID (I need these fields to reflect any updated information from the HardwareDB)

 

[SemperFiDownUnda]

Triggers are just a special type of SP that get executed after the data has hit the transaction log but before they are commited. There are 2 memory tables available in them DELETED and INSERTED. For a DELETE trigger you'll have DELETED, for a INSERT TRIGGER you'll have INSERTED, for an UPDATE trigger you'll have both.

Avoid large processing in a trigger as any record effected by the source statement is locked until the trigger is finished.

Check out Books Online for more info.