Re: Triiger -vs- Constraint

(csomberg@xxxxxxx) writes:
> SQL Server 2000 SP4
>
> I was wondering what has the best performance for maintaining
> referential integrity ... triggers, constraints etc .....

An AFTER trigger is almost bound to give worse performance, because by
the time when the trigger is entered, the data is already there in the
table, thus an error causes a rollback.

An INSTEAD OF trigger could hypothetically be faster when something
goes wrong, because nothing has happned yet.

Then again, triggers offers some good performance traps - the tables
"inserted" and "deleted" are constructed from the transaction log
and access to them can be slow. If you are to make several accesses to
these tables in the trigger, it is often better to to insert the
data into table variables instead.

So I would say that constraints in the normal case is faster than triggers.

In any case, you need to master both. As Hugo says, constraints are so
much simpler to implement, that that is reason alone to use them whenever
possible.

On the other hand, far from everthing can be handled in constraints, so
triggers are more general.


So are there cases when triggers beats constraints? Yes. Say that you
have an CustomerCategories table that has an IsActive flag. An active
customer must belong to a customer category which also is active. This
can be implemented with a constraint, if you use a UDF to check the
status of the customer category. Luckily, most people wouldn't think
of this, and use a trigger instead. I played with this on a table with
30000 rows. An update of all rows went from one second to thirty...


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

Relevant Pages

  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Re: New to SQL server
    ... SQL SERVER WORKS LIKE A CHARM, PLEASE EXPLAIN YOUR SO CALLED BUGS ... Both Cascade referential actions in DRI and table-level CHECK ... self-referencing FKs and inline constraints. ... you could write a trigger to perform the cascade action for ...
    (microsoft.public.access.adp.sqlserver)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)
  • Re: Access Bug with SQL Server identity columns
    ... Even though SQL Server since version 2000 has had a reliable ... The work-around I've always used is to make sure that no trigger inserts into ... a table that contains an IDENTITY column, and use some other unique identifier ... >create a form on table1 with a subform on table2. ...
    (comp.databases.ms-access)