Subject Re: [firebird-support] Check constraints vs. user defined triggers
Author Aldo Caruso
Hi Ann,

    I ran a test and there is no performance difference, so I'll keep using check constraints as they are simpler.
    Thanks for your suggestion.

El 04/01/15 a las 15:31, Ann Harrison aharrison@... [firebird-support] escibiĆ³:

> On Dec 29, 2014, at 9:56 PM, Aldo Caruso aldo.caruso@... [firebird-support] <> wrote:
No one has answered, so I'll offer a not-very interesting suggestion.
> I have a table with four check constraints, which basically check
> logical conditions between its fields.
> In order to enforce those constraints, Firebird creates behind the
> scenes two triggers for each one of them (I suppose one for update and
> the other for insert operation). So in this case it ends up with eight
> system created triggers.
> I suppose this degradates performance of massive insert or updates, as
> four triggers have to be launched for each insert or update operation.

Triggers aren't launched like separate programs, but get incorporated in the compiled request so eight small triggers have about the same performance impact as one large trigger.
> Does it have sense in order to enhace performance not to use check
> contraints but instead check those constraints globally using only two
> user defined triggers (before insert and before update), raising
> exceptions when logical conditions aren't met ?
> Is it worthwhile ? Which are the pros and cons of this approach ?

In your situation, I'd build a test and measure the diffence in performance of the two approaches, even though in theory there shouldn't be much.

Good luck,