title: Stored procedure for referential integrity between two tables in different database?
body: <p>For two tables A and B, I'd like to implement referential integrity such that in tables A a foreign key's value must present in table B, while in table B a primary key can only be deleted or modified if that value does not present in table A. My requirement is that I'd like to have table A, and B as variable, and apply the procedure to any arbitrary instances of tables. That is, </p>
  
  <pre><code>sp_referential_integrity_across_databases(A, B)
  </code></pre>
  
  <p>I have figured out how to do the referential integrity as triggers for a pair of particular tables. I wonder if it's feasible to write such stored procedure to save future effort?</p>
  
  <p>My environment is Microsoft SQL Server 2017. The more portable the solution, the better. </p>
  
  <p>Here are my crafted procedures:</p>
  
  <p>The triggers on table "A" for insert and update:</p>
  
  <pre><code>USE DWPractice
  IF OBJECT_ID ('dbo.trgCheckCustomer_Cat_Id_Customer_D', 'TR') IS NOT NULL
  DROP Trigger trgCheckCustomer_Cat_Id_Customer_D;
  GO
  CREATE TRIGGER trgCheckCustomer_Cat_Id_Customer_D
  ON Customer_D
  AFTER INSERT, UPDATE
  AS
  IF NOT EXISTS
  (
  SELECT Customer_Cat_Id
  FROM inserted
  WHERE Customer_Cat_Id IN (SELECT Customer_Cat_Id FROM [OtherDW].[dbo].[Customer_Cat_D])
  )
  BEGIN
  RAISERROR('Lookup Value Not Found -- Inerst Failed', 16, 1);
  ROLLBACK TRANSACTION;
  END;
  </code></pre>
  
  <p>The trigger on table "B" for delete and update:</p>
  
  <pre><code>USE OtherDW
  IF OBJECT_ID ('dbo.trgCheckCustomer_Cat_Id_Customer_Cat_D', 'TR') IS NOT NULL
  DROP Trigger trgCheckCustomer_Cat_Id_Customer_Cat_D;
  GO
  CREATE TRIGGER trgCheckCustomer_Cat_Id_Customer_Cat_D
  ON Customer_Cat_D
  AFTER DELETE, UPDATE
  AS
  Begin
  IF EXISTS
  (
  SELECT Customer_Cat_Id
  FROM deleted
  WHERE Customer_Cat_Id IN (SELECT Customer_Cat_Id FROM [DWPractice].[dbo].[Customer_D])
  )
  BEGIN
  RAISERROR('Lookup Value Found -- Delete Failed', 16, 1);
  ROLLBACK TRANSACTION;
  END;
  
  -- It seems that the following for the case of update is not needed
  -- The above clauses would get executed even for the case of update.
  -- IF EXISTS
  -- (
  -- SELECT Customer_Cat_Id
  -- FROM inserted
  -- WHERE Customer_Cat_Id IN (SELECT Customer_Cat_Id FROM [DWPractice].[dbo].[Customer_D])
  -- )
  -- BEGIN
  -- RAISERROR('Lookup Value Found -- Update Failed', 16, 1);
  -- ROLLBACK TRANSACTION;
  -- END;
  End;
  </code></pre>
  
  <p>If stored procedure is not the best practice, then what is the best practice? It seems to me, there's much boiler-plate code, with only database name and table name are variables.</p>