Relating databases

[sunaj]

Hi,

I want to relate 2 tables in 2 different databases (like a foreign key). I understand that that can be done by using triggers. How?
Does anyone have or know where I can find an example that shows this?

Thanks,

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[RickCole]

Not fully sure I understand what you are trying to achieve but here is an example of a delete trigger.

The trigger stops you deleting records off say a parent table whilst there are associated child records in a table on another server and database.


CREATE TRIGGER PARENT_TABLE_D_TRIG ON dbo.PARENT_TABLE
FOR DELETE
AS
IF (SELECT COUNT(*) FROM
DELETED A, OTHER_SERVER.OTHER_DB.dbo.CHILD_TABLE B
WHERE A.PK_ID=B.PK_ID) <> 0
BEGIN
RAISERROR ('Associated Records in Child Table',16,-1)
ROLLBACK TRANSACTION
RETURN
END

GO



Rick.