 |
|
SQL Server Tips by Burleson |
Referential Integrity with triggers
Using DRI is very easy and it is faster than triggers, why use
triggers for referential integrity?
One reason is that cyclic relationships sometimes are not allowed by
DRI, another one is that DRI in SQL Server is not fully ANSI SQL-92
compliant when it comes to the cascading actions.
DRI forbids cascading updates or deletes in at least five scenarios:
- Cyclic relationships between tables – when the cascade path
goes from table A to table B and then back to A. A->B->A
- Inner relationships – when the cascade path goes from column
col1 in table A to column col2 also in table A. A->A
- Concurrent cascade paths - when the cascade path goes from
table A to table B and table A to table C plus from table B to
table C. A->B->C and A->C
- Multiple cascade paths – when the cascade path goes from
column col1 in table A to table B and also column col2 in table
A to table B. A->B (col1) and A->B (col2)
- Existence of INSTEAD OF triggers in the cascade paths - when
the cascade path goes from table A to table B and table B has an
INSTEAD OF trigger.
ANSI SQL-92 actions:
No action.
Cascade.
Set default.
Set NULL.
DRI will enforce the first two actions but triggers allow the other
two by using code to implement them.
For the next example let us create the same tables as before but
with no foreign key:
CREATE TABLE [Tbl A] ([col A] [varchar]
(50) NOT NULL)
GO
ALTER TABLE [Tbl A] ADD CONSTRAINT
[PK_Tbl A] PRIMARY KEY CLUSTERED ([Col A])
GO
CREATE TABLE [Tbl B] ([Col B] [varchar] (50))
The related table will have no foreign key but a trigger will
enforce the foreign key constraint.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
