Board index » delphi » RI Question: Declarative vs Trigger

RI Question: Declarative vs Trigger


2006-12-12 08:43:51 AM
delphi183
I am using SQL Server 7, 2000 and 2005.
All of my cascading RI is handled via triggers (generated by data modeller).
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
The reason I ask is because I currently still support SQL 7 and I'd like
to avoid separate app versions of the database per SQL Server version.
 
 

Re:RI Question: Declarative vs Trigger

"Isaac Alexander" <XXXX@XXXXX.COM>writes
Quote
I am using SQL Server 7, 2000 and 2005.

All of my cascading RI is handled via triggers (generated by data
modeller).

From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?

The reason I ask is because I currently still support SQL 7 and I would
like to avoid separate app versions of the database per SQL Server
version.
I know for Interbase, triggers don't operate under the same transactional
context as relational constraints, so they are not the same. Not sure about
SQL Server, though.
Dan
 

Re:RI Question: Declarative vs Trigger

Quote
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
A trigger in SQL Server eliminates the ability to do in place updates,
because it instead has to do an insert and delete. However, you may be able
to code a trigger to affect locks etc. a lot less than declarative RI would.
If your declarative RI is simple, I would stick with that. If it needs to be
specialized, triggers will be better.
Oliver Townshend
 

Re:RI Question: Declarative vs Trigger

"Oliver Townshend" <oliveratzipdotcomdotau>writes
Quote
>From a performance point of view (100 million plus recs in a table), is
>it better to use declarative RI or trigger RI?

A trigger in SQL Server eliminates the ability to do in place updates,
because it instead has to do an insert and delete. However, you may be
able to code a trigger to affect locks etc. a lot less than declarative RI
would.

If your declarative RI is simple, I would stick with that. If it needs to be
specialized, triggers will be better.

My triggers are only there for RI. Most are cascading or setting null.
 

Re:RI Question: Declarative vs Trigger

"Andrew V. Fionik" <XXXX@XXXXX.COM>writes
Quote
Hello, Isaac!
You wrote on Mon, 11 Dec 2006 16:43:51 -0800:

IA>All of my cascading RI is handled via triggers (generated by data
IA>modeller).
IA>From a performance point of view (100 million plus recs in a table),
is
IA>it better to use declarative RI or trigger RI?

If your RI is simple then use declarative. Use triggers in complex places.
Depends on the database. For Interbase, RI and triggers don't work the same
way:
www.cvalde.net/document/declaRefIntegVsTrig.htm
Dan