Lists: | pgsql-general |
---|
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 |
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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | mmiranda(at)americatel(dot)com(dot)sv |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: After insert trigger question |
Date: | 2005-04-27 17:09:45 |
Message-ID: | 20050427170945.GA62136@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Wed, Apr 27, 2005 at 10:38:48AM -0600, mmiranda(at)americatel(dot)com(dot)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/