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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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