Firdt idea: NULL's usually need to be handled separately because
nothing equals NULL, not even NULL. You may only check for NULL using
the "is null" operator.

Second idea: Testing for recursivity should be easy. Set up a dummy
table, say RECURSIVE_COUNTER with only one field (COUNTER).

First line on your trigger, before you do any actual work:

update recursive_counter set counter = counter + 1;
select counter from recrusive_counter into :counter;
if counter > 1 then exception e_something;

The last line on your trigger:

update recursive_counter set counter = counter - 1;

This should detect recursivity and raise an exception if that ever
happens.

--
Cosmin Prund

--- In firebird-support@yahoogroups.com, "Ross Ryding" <ross@...>
wrote:

>
> Hello,
>
> Guess I am being slow this morning, why handle NULLS, should

they not be

> a miss-compare and thus write a record to the secondary table

(audit table)?

> Do not understand infinite recursion to check for. Basically the

trigger is

> fired once when the mrp screen moves to another screen. During

that time 1

> or more fields could have been changed (I get one trigger for many

updates)

> at that point I test the fields and update ones that are

appropriate. Could

> you please explain in a little more detail.
>
>
>
> Thanks ross
>
>
>
> _____
>
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Alexandre

Benson

> Smith
> Sent: Tuesday, July 10, 2007 5:10 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] multiple inserts in trigger
>
>
>
> Ross Ryding wrote:
> > I am trying to do multiple inserts to the same table in a

trigger.

> > The purpose of the script is to provide an audit of a table. The
> > trigger is AFTER UPDATE on my JOBS table. The inserts are going

to

> > DO_AUDIT which is a table to just monitor changes to tables. If i
> > then run the application and modify any one field it funtions

fine,

> > if I modify 2 or more fields it hangs.
> >
> > Basically it tests each field for old <> new and if that is true

it

> > does an insert into the table with information necessary to audit.
> >
> > If two of the tests or more are true, it hangs from the

aplllication

> > point of view. I am sure it is something to do with locking or
> > multiple insert context but I can find nothing in the firebird
> > manual, in fact they imply there is no issue.
> >
> > thanks ross
> >
> >
>
> Ross,
>
> Not the answer to your question, but I think you should provide

logic to

> handle null's on your trigger.
>
> Had you checked if you are getting some kind of infinite recursion

with

> this trigger ?
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>
>
>
>
>
> [Non-text portions of this message have been removed]
>