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