On Wed, Apr 27, 2005 at 05:24:16PM +0200, Stephane Bortzmeyer wrote: > On Wed, Apr 27, 2005 at 08:45:44AM -0600, > mmiranda@xxxxxxxxxxxxxxxxx <mmiranda@xxxxxxxxxxxxxxxxx> 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/ ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
