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,
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
