Thread: Trigger question

Hi there.

I'm trying to use triggers for the first time, but I'm having some
problems.

Here's my SQL:

create function set_counter()
returns opaque
as 'BEGIN
    new.count := 0;
    RETURN new;
    END;'
language 'plpgsql';

create trigger set_counter after insert or update on nivel
for each row execute procedure set_counter();


count is an int, and its default value is -1.

The problem is that after I insert something, count value is still -1,
not 0!

Could someone tell me what's wrong?

Thanks


--
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil

Nelio Alves Pereira Filho <nelio@ifx.com.br> writes:
> create trigger set_counter after insert or update on nivel
> for each row execute procedure set_counter();

> The problem is that after I insert something, count value is still -1,
> not 0!

You want a BEFORE trigger, not an AFTER trigger.  AFTER is too late
to affect the contents of the stored row.

            regards, tom lane

I got it.
But here's what I need to do: after any modification on the table, I
must run a procedure that will do some calculation, and then set these
values for each row. This calculation establishes relationships between
rows, and builds logically a tree. To accomplish all that, first I need
to unset some values, so I decided to use the BEFORE trigger to unset
these values, and the AFTER trigger to do this calculation.

Will I have to use explicit queries? And what's the real purpose of the
AFTER trigger?

Thanks


Tom Lane wrote:
>
> Nelio Alves Pereira Filho <nelio@ifx.com.br> writes:
> > create trigger set_counter after insert or update on nivel
> > for each row execute procedure set_counter();
>
> > The problem is that after I insert something, count value is still -1,
> > not 0!
>
> You want a BEFORE trigger, not an AFTER trigger.  AFTER is too late
> to affect the contents of the stored row.
>
>                         regards, tom lane

--
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil

Nelio Alves Pereira Filho <nelio@ifx.com.br> writes:
> To accomplish all that, first I need
> to unset some values, so I decided to use the BEFORE trigger to unset
> these values, and the AFTER trigger to do this calculation.

Why don't you just do all the work in the BEFORE trigger?

> And what's the real purpose of the AFTER trigger?

The sequence of events is

    compute contents of tuple to insert/update (per the query)

    apply BEFORE triggers

    check constraints

    insert tuple

    apply AFTER triggers

You *must* use a BEFORE trigger if you want to alter the tuple
that was computed by the query.  The point of an AFTER trigger
is that it won't be run until constraints have been passed,
so you can avoid doing potentially expensive work until you know
the tuple is good.  You might use an AFTER trigger to propagate
changes into other tables, for example.  But you can't change
the tuple.

            regards, tom lane

> > these values, and the AFTER trigger to do this calculation.
>
> Why don't you just do all the work in the BEFORE trigger?

For instance, If I do an UPDATE inside a function that will be triggered
by an UPDATE, won't it loop forever?

Does PostgreSQL support recursive functions?

Thanks