Re: After insert trigger question

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr>
Cc: mmiranda(at)americatel(dot)com(dot)sv, pgsql-general(at)postgresql(dot)org
Subject: Re: After insert trigger question
Date: 2005-04-27 16:17:05
Message-ID: 20050427161705.GA52440@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Apr 27, 2005 at 05:24:16PM +0200, Stephane Bortzmeyer wrote:
> On Wed, Apr 27, 2005 at 08:45:44AM -0600,
> mmiranda(at)americatel(dot)com(dot)sv <mmiranda(at)americatel(dot)com(dot)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/

In response to

Browse pgsql-general by date

  From Date Subject
Next Message mmiranda 2005-04-27 16:38:48 Re: After insert trigger question
Previous Message Ned Lilly 2005-04-27 16:04:17 Re: OpenRPT (was Re: [ANNOUNCE] == PostgreSQL Weekly