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> wroteI 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.
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
Import Notes
Resolved by subject fallback
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/