Thread: loop on trigger

loop on trigger

From
mgermoni@uniautomation.it
Date:
following your indication I found what I was looking for...Thank you.

I made a simple trigger/function that:
on insert check if a particular field is already on one table if not it
will procede to make the insert otherwise not.
But it goes on loop. Now just because I do not have experience, I'm
wondering if it possible to do it without going in loop, because if the
trigger is build up to get fired before the action and inside itself,
actualy, there is again the continuos of the action it is called again
and so on. There is a method to do without using the same table to
do the control and then the action? In other words interception an
insert to a specific table if the control is true to procede with the
action itself without hitting any loop?
TX
==============================
Marco GERMONI            E-mail:mgermoni@uniautomation.it
Field Engineering Manager
Uniautomation S.r.l.               tel.+39-06-514221
Via A. Di Bonaiuto, 39             fax +39-06-51422311
I-00142 Roma Italy                 GSM +39-348-9997186

Internet: www.uniautomation.it
==============================

Re: loop on trigger

From
"Richard Huxton"
Date:
mgermoni@uniautomation.it said:

> following your indication I found what I was looking for...Thank you.
>
> I made a simple trigger/function that:
> on insert check if a particular field is already on one table if not it
> will procede to make the insert otherwise not.
> But it goes on loop. Now just because I do not have experience, I'm
> wondering if it possible to do it without going in loop, because if the
> trigger is build up to get fired before the action and inside itself,
> actualy, there is again the continuos of the action it is called again
> and so on. There is a method to do without using the same table to
> do the control and then the action? In other words interception an
> insert to a specific table if the control is true to procede with the
> action itself without hitting any loop?
> TX

Had the same problem myself. I don't believe there is a way to "short-circuit"
the loop.

See if you can't replace the trigger with a view and a couple of rules. You'll
need one rule to handle the default case and a second to deal with the special
case.

If you need more information, could you please provide the details of your
tables and trigger.

HTH

- Richard Huxton

Re: loop on trigger

From
will trillich
Date:
On Wed, Apr 18, 2001 at 10:06:01PM +0200, mgermoni@uniautomation.it wrote:
> following your indication I found what I was looking for...Thank you.
>
> I made a simple trigger/function that:
> on insert check if a particular field is already on one table if not it
> will procede to make the insert otherwise not.
> But it goes on loop. Now just because I do not have experience, I'm
> wondering if it possible to do it without going in loop, because if the
> trigger is build up to get fired before the action and inside itself,
> actualy, there is again the continuos of the action it is called again
> and so on. There is a method to do without using the same table to
> do the control and then the action? In other words interception an
> insert to a specific table if the control is true to procede with the
> action itself without hitting any loop?

imo a better approach would be to create a view, and define an
insert rule that intercepts attempts to insert to that,
re-routing the data to the real table instead:

    create table _real_stuff (
    ...
    );

    create view read_only as select * from _real_stuff;

    create rule myInsert as
        on insert to read_only
        do instead
            insert into _real_stuff (fieldlist)
                values (NEW.this,NEW.that,NEW.one+NEW.two-NEW.three);

    create rule myUpdate as
        on update to read_only
        do instead
            update _real_stuff set
                field1 = NEW.field1,
                field2 = OLD.field3 - NEW.field72
            where
                primarykeyfield = NEW.primarykeyfield;

that way you won't trigger your own rule within your own rule
within your own rule within your own rule...

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!