Stored procedure for referential integrity between two tables in different database?


Keywords:sql 


Question: 

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,

sp_referential_integrity_across_databases(A, B)

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?

My environment is Microsoft SQL Server 2017. The more portable the solution, the better.

Here are my crafted procedures:

The triggers on table "A" for insert and update:

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;

The trigger on table "B" for delete and update:

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;

1 Answer: 

The logic in your (first) trigger is not correct. If you have multiple rows in inserted, then only one has to match. Instead, you want:

CREATE TRIGGER trgCheckCustomer_Cat_Id_Customer_D ON Customer_D AFTER INSERT, UPDATE
AS BEGIN
    IF EXISTS (SELECT 1
               FROM inserted i LEFT JOIN
                    [OtherDW].[dbo].[Customer_Cat_D] d
                    ON i.Customer_Cat_Id = d.Customer_Cat_Id
               WHERE d.Customer_Cat_Id IS NULL
              )
    BEGIN
        RAISERROR('Lookup Value Not Found -- Insert Failed', 16, 1);
        ROLLBACK TRANSACTION;
    END;
END; -- trigger