Illegal recursion in rule evaluation error in trigger

Hi All,

I have an update trigger written on a table. The stored procedure for the trigger is as follows:

PROCEDURE updTicketTrigger
DIMENSION tcount(1) as Integer
select count(*) from <tablename> where <condition> into ARRAY tcount noconsole
IF (tcount[1]) = 1
tcount[1] = 0
FOR i = 1 TO 500
DO WHILE .t.
&&setting value1 and value2 for field1,field2
ENDDO

INSERT INTO tablename(field1, field2) values(value1, value2)
ENDFOR

ENDIF
RETURN .t.
ENDPROC

On execution of the trigger, the following error message is displayed on the very first insert itself!
Illegal recursion in rule evaluation.

Can't we use loops in triggers?

Any help is greatly appreciated.
Regards.

Andrew
Wed Jun 30 04:11:32 CDT 2004

sm wrote:
> Hi All,
>
> I have an update trigger written on a table. The stored procedure for
> the trigger is as follows:
>
> PROCEDURE updTicketTrigger
> DIMENSION tcount(1) as Integer
> select count(*) from <tablename> where <condition> into ARRAY tcount
> noconsole
> IF (tcount[1]) = 1
> tcount[1] = 0
> FOR i = 1 TO 500
> DO WHILE .t.
> &&setting value1
> and value2 for field1,field2 ENDDO
>
> INSERT INTO tablename(field1, field2) values(value1, value2)
> ENDFOR
>
> ENDIF
> RETURN .t.
> ENDPROC
>
> On execution of the trigger, the following error message is displayed
> on the very first insert itself! Illegal recursion in rule evaluation.
>
> Can't we use loops in triggers?

I don't work with the triggers, rules and so on [only FPW here] but from
what I understand, your update trigger is called when you update the table.
In your update trigger you have inserted records which is an event that will
cause the trigger to fire. I think that is the recursion problem you have.

--
HTH
Andrew Howell

Stefan
Wed Jun 30 04:23:38 CDT 2004

> INSERT INTO tablename ...

Sounds as if the target "tablename" is the same as the trigger's
owner table, right? If so, in Vfp you cannot self-update a table in
its own update trigger, literally because of the resulting recursion.

Alternatively, you could use the table's "Record validation rule"
(although I think maybe you don't want to, as it seems you're
storing some calculated values depending on other columns,
which breaks "normalization" rules and actually almost always
makes app design / code maintenance much more complicated)


hth
-Stefan

"sm" <sm@discussions.microsoft.com> schrieb im Newsbeitrag
news:B5DDF816-5F91-4F3C-B0EF-72561790B177@microsoft.com...
> Hi All,
>
> I have an update trigger written on a table. The stored procedure for the trigger
is as follows:
>
> PROCEDURE updTicketTrigger
> DIMENSION tcount(1) as Integer
> select count(*) from <tablename> where <condition> into ARRAY tcount noconsole
> IF (tcount[1]) = 1
> tcount[1] = 0
> FOR i = 1 TO 500
> DO WHILE .t.
> &&setting value1 and value2 for
field1,field2
> ENDDO
>
> INSERT INTO tablename(field1, field2) values(value1, value2)
> ENDFOR
>
> ENDIF
> RETURN .t.
> ENDPROC
>
> On execution of the trigger, the following error message is displayed on the very
first insert itself!
> Illegal recursion in rule evaluation.
>
> Can't we use loops in triggers?
>
> Any help is greatly appreciated.
> Regards.
>