Skip site navigation (1) Skip section navigation (2)

Re: After insert trigger question

From: mmiranda(at)americatel(dot)com(dot)sv
To: pgsql-general(at)postgresql(dot)org
Subject: Re: After insert trigger question
Date: 2005-04-27 16:38:48
Message-ID: 76E0DAA32C39D711B6EC0002B364A6FA0440F8E6@amsal01exc01.americatel.com.sv (view raw or flat)
Thread:
Lists: pgsql-general
>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


Responses

pgsql-general by date

Next:From: Typing80wpmDate: 2005-04-27 16:43:52
Subject: Serial / auto increment data type
Previous:From: Michael FuhrDate: 2005-04-27 16:17:05
Subject: Re: After insert trigger question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group