Thread: After insert trigger question

Hi ppl,
i have a specific question about insert triggers, in the docs i found that
you can change the value of an inserted column using the following syntax:

NEW.column_name := value

and then if you return NEW the new value is stored instead of the original.
this is true if it is a before insert trigger.
The manual also says that the return value of an after insert trigger is
ignored, that means that you cannot update the value of a column in the same
way with an after insert trigger?.
I am concerned about how reliable is an before insert trigger, i made some
computation in my trigger and i want that no matter what happens inside  the
trigger (exceptions, erros, divide by zero, etc) , the row must be inserted,
i mean if the trigger fails,  i always have the row in my table.
Because of that, i think after insert trigger is the best option, beacuse is
fired after the data is in the table, am i wrong?
thanks

On Wed, Apr 27, 2005 at 08:45:44AM -0600,
 mmiranda@americatel.com.sv <mmiranda@americatel.com.sv> wrote
 a message of 21 lines which said:

> I am concerned about how reliable is an before insert trigger, i
> made some computation in my trigger and i want that no matter what
> happens inside the trigger (exceptions, erros, divide by zero, etc)
> , the row must be inserted,

I do not think that pl/pgsql has exception handlers
(http://www.postgresql.org/docs/7.4/interactive/plpgsql-errors-and-messages.html). You
can raise exceptions but not catch them. Could you rewrite your
trigger function with another programming language? In Python, it
would be something like (not tested):

try:
   ... your computations
finally:
   # Insert anyway
   return "OK"

On Wed, Apr 27, 2005 at 05:24:16PM +0200, Stephane Bortzmeyer wrote:
> On Wed, Apr 27, 2005 at 08:45:44AM -0600,
>  mmiranda@americatel.com.sv <mmiranda@americatel.com.sv> wrote
> >
> > I am concerned about how reliable is an before insert trigger, i
> > made some computation in my trigger and i want that no matter what
> > happens inside the trigger (exceptions, erros, divide by zero, etc)
> > , the row must be inserted,
>
> I do not think that pl/pgsql has exception handlers
> (http://www.postgresql.org/docs/7.4/interactive/plpgsql-errors-and-messages.html).

PostgreSQL 8.0 introduced PL/pgSQL exception handlers.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regardless of whether the trigger is BEFORE or AFTER, an untrapped
error will abort the insert.

CREATE FUNCTION trigfunc() RETURNS trigger AS '
DECLARE
    i  integer;
BEGIN
    i := NEW.x / 0;
    RETURN NULL;
END;
' LANGUAGE plpgsql;

CREATE TABLE foo (x integer);

CREATE TRIGGER footrig_after AFTER INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

INSERT INTO foo VALUES (123);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function "trigfunc" line 4 at assignment

SELECT * FROM foo;
 x
---
(0 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

>PostgreSQL 8.0 introduced PL/pgSQL exception handlers.

>http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.h
tml#PLPGSQL-ERROR-TRAPPING

>Regardless of whether the trigger is BEFORE or AFTER, an untrapped
>error will abort the insert.

>CREATE FUNCTION trigfunc() RETURNS trigger AS '
>DECLARE
>    i  integer;
>BEGIN
>    i := NEW.x / 0;
>    RETURN NULL;
>END;
>' LANGUAGE plpgsql;

>CREATE TABLE foo (x integer);

>CREATE TRIGGER footrig_after AFTER INSERT ON foo
>  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

>INSERT INTO foo VALUES (123);
>ERROR:  division by zero
>CONTEXT:  PL/pgSQL function "trigfunc" line 4 at assignment

>SELECT * FROM foo;
> x
>---
>(0 rows)

>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/


So, the answer is:  "double check every operation and use exeption handlers"
What about performance, if its a matter of choice between after or before
insert, what perform better?
thanks

On Wed, Apr 27, 2005 at 10:38:48AM -0600, mmiranda@americatel.com.sv wrote:
>
> What about performance, if its a matter of choice between after or before
> insert, what perform better?

According to the "Triggers" chapter in the documentation, "If you have
no specific reason to make a trigger before or after, the before case
is more efficient, since the information about the operation doesn't
have to be saved until end of statement."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/