RI Enforcement: Trigger-Based or ??

On my first SQL Server DB, I used MS Access as my database design tool and then
converted it.
Not knowing much, I made a more-or-less random choice and opted for
Trigger-Based RI enforcement.
I know there's another method, but haven't got a clue as to the tradeoffs.
Eventually, I'll spring for a "real" design tool and at that point need to be
able to make a more informed decision.
Comments?
--
PeteCresswellRE/
>I know there's another method, but haven't got a clue as to the tradeoffs.
Actually, I think I may have a single small clue: With trigger-based RI
enforcement, you have the opportuntity to customize the failure messages... but
that seems trivial...
--
PeteCresswell|||The standard way to enforce RI is through a Foreign Key constraint:
CREATE TABLE Table_A (A INTEGER PRIMARY KEY)
CREATE TABLE Table_B (B INTEGER PRIMARY KEY, A INTEGER NOT NULL
CONSTRAINT constraint_name REFERENCES Table_A (A))
This is much more concise than the equivalent trigger and generally should
perform better than procedural trigger code. A foreign key can also be used
by the optimizer to build more efficient query plans and it can be used by
design and admin tools (such as Enterprise Manager) to determine
relationships. Triggers won't do those things.
Some reasons for using a trigger instead might be: to implement complex
validation that can't otherwise be defined in a CHECK or FK constraint; to
implement cascading RI where the CASCADE option isn't permitted.
--
David Portas
--
Please reply only to the newsgroup
--sql