wim.buyens (3/5/2010)
This can be done with trigger and procedures I think but it won't be easy.You can't use a foreign key based on some condition.
A trigger in which you use a procedure with dynamic statements maybe can do the trick.
Haven't tried this yet, if a have some time I'll try next week.
On a second read you are absolutely correct - triggers would do the trick.
Microsoft calls it "Procedural Referential Integrity" as opposed to "Declarative Referential Integrity" which is based in FK/PK pairs.
More information is available at http://msdn.microsoft.com/en-us/library/aa902684(SQL.80).aspx#sql_refintegrity_topic03
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.