Find FK/Rel info when using triggers for RI

[SteveDingle]

Hello All,

I am using a data modelling tool (xCase) to maintain my SS2K database. It allows me 2 ways to maintain Referential Integrity - via triggers or via Declarative RI which I see is FK Constraints.

I had chosen to use triggers because I had read in the end hey are more flexible and powerful. However I now find that for certain processes I need to extract the relationship information from SQL Server 2K ( like which columns are FKs and to which table). I know I can use sp_fkeys to get this information but it but there doesn't seem to be any entries in my database. My assumption is that that information is done via the FK Constraints. So I have a coupla questions:

1) Is this information stored any where else and should my data modeling tool be adding it (i.e. is it the tools fault)

And if so, how would my tool add that information

2) If I do need contraints.. can I have both or is that a no-no. AIf I can have both, which gets called first. Contraint or trigger.

Any thoughts and ideas would be greatly appreciated


Thanks
Steve

 

[SQLSister]

If you can do something in a constraint, it is generally more efficient than doing in a trigger. It is a best practice to use constraints wherever possible. You certainly can have both constraints and triggers.

The answer to your question on when constraints vice triggers occur is in BOL. In the index, the article is: constraints, vs. triggers
(that's an exact quote just paste it in the index search box).

There is a lot of other good information in this article which is why I'm pointing you to it instead of telling you waht is says.

"NOTHING is more important in a database than integrity." ESquared

 

[SteveDingle]

Thanks SQLSister, will look at the article you mentioned. one follow up if I may. Apologise in advance if it is in the BOL somewhere (couldn't find it).

Another reason I used trigger was because I could use my data modeling tool to create trigger code with specific information Error which I can then display directly to my users

SELECT @error_number=500001,
@error_message='Unable to delete from.... '
RAISERROR @error_number @error_message

I assume there is no way to do something similar with a FK constraint

Thanks again

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[SQLSister]

Not that I know of.

"NOTHING is more important in a database than integrity." ESquared