question

Katie 1 avatar image
Katie 1 asked

Maintaining constraints across different databases

What are the different approaches to maintain constraints accross different databases? what are the pro's and con;s of the process, If creating constraints is not possible, what is the best way of doing it? I was also wondering if there is a possibility that, we create a trigger on a datasbase table A residing in the database 1 To insert a new record in the table B in the database 2, When ever there is a new record in the table A? Any advice is appreciated. Thanks,
sql-server-2008sqlt-sqldatabase-designconstraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Constraints between tables among different databases are only possible using the Triggers. The ON UPDATE and ON DELETE rules you can simply implement by the AFTER UPDATE, DELETE triggers. The referential integrity you can enforce by the INSTEAD OF triggers. There was a similar question in past about this. You can create a simple AFTER INSERT Trigger, which will insert data to a table in other database. CREATE TRIGGER dbo.trg_aTable_AI ON dbo.aTable AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO [otherdb].[dbo].[OtherTable](Col1, Col2) SELECT Col1, Col2 FROM INSERTED END GO
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you !
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.