Indeed:
**********************************************************************
James,
Here is part of a message I posted here earlier in response to a similar
question:
===================================================================
You are correct that updating the table that fired an update trigger will
result in an ongoing loop. The way I handle that is to have the trigger set
a variable when it executes like this:
{top of code}
SET VAR trigger_executed INTEGER
IF trigger_executed = 1 THEN
CLEAR VAR trigger_executed
RETURN 0
ENDIF
... {body of trigger}
{end of code}
SET VAR trigger_executed INTEGER = 1
RETURN 0
On the first execution of the trigger the IF fails since the variable has no
value. At the end of the trigger it sets the variable to 1. On the second
invocation of the trigger it sees the variable has a value of 1, clears the
variable and exits.
This problem does not occur if you use an AFTER INSERT trigger, except to
note that the UPDATE trigger will be fired.
===================================================================
This technique *can* be used to update a row just inserted (AFTER INSERT),
or to timestamp an updated row (AFTER UPDATE). The code above is only
required in the AFTER UPDATE trigger to prevent the update from firing the
trigger endlessly.
Now, if you have separate timestamp fields for inserts and updates you'll
need to set up the same semaphore in the AFTER INSERT trigger (SET VAR
trigger_executed INTEGER = 1) to tell the AFTER UPDATE trigger to not do
anything when inserts occur.
**********************************************************************
Emmitt Dove
Manager, Converting Applications Development
Evergreen Packaging, Inc.
[email protected]
(203) 214-5683 m
(203) 643-8022 o
(203) 643-8086 f
[email protected]
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of MikeB
Sent: Friday, January 30, 2009 3:22 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: ERROR- Specified default value is not valid for
column Re...
I believe Emmitt employed a simple counter var in the trigger to overcome
the endlesssssssssss loooooop issue.
----- Original Message -----
From: "James Bentley" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Friday, January 30, 2009 2:12 PM
Subject: [RBASE-L] - Re: ERROR- Specified default value is not valid for
column Re...
> Karen,
>
> I prefer to control such action in TRIGGERS because in my applications
> form are not the only method of inserting and updating records. With my
> address tables I need to keep track of who and when actions were
> performed. I was able to partially solve my problem by a major redesign of
> the structure by placing audit information in the master table instead of
> the subordinate address table. I had to resort to this because RBase
> implements BEFORE/AFTER INSERT and UPDATE TRIGGERS as read only cursors.
> When I tried to change audit fields in the subject record during the
> TRIGGER is created an endless loop that turminate when I exceeded some
> internal limit. I know of several competitive databases that implement
> TRIGGERS with an updateable cursor.
> I have had such an enhancement request in for several years to make the
> BEFORE INSERT and the BEFORE UPDATE triggers have an updateable cursor.
>
> Maybe some day.
>
> Jim Bentley
> American Celiac Society
> [email protected]
> tel: 1-504-737-3293
>
>
> --- On Fri, 1/30/09, [email protected] <[email protected]> wrote:
>
>> From: [email protected] <[email protected]>
>> Subject: [RBASE-L] - Re: ERROR- Specified default value is not valid for
>> column Re...
>> To: "RBASE-L Mailing List" <[email protected]>
>> Date: Friday, January 30, 2009, 7:50 AM
>> Jim: You're right, we couldn't use a computed
>> column either. I think in
>> our case the updating of this date column would have been
>> done only in 2 places
>> within a form, so we made a form variable instead for
>> Colname = .#DATE so
>> that the date would update if we entered a row or saved a
>> change. Could also
>> handle this with a trigger if you want to keep track of
>> r> prompt changes or
>> other programs that might do an Update.
>>
>> Karen
>>
>>
>> > Using a computed column doesn't work for me as a
>> reload resets the value
>> > whereas I am using such a field to track changes for
>> audit purposes.
>> >
>> > Perhaps in v9.0.
>> >
>> > Jim Bentley
>
>
>
>
>
