Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter