Hi there, I'm using PG 8.2.3.
Is it possible to (somehow) commit a specific statement in a trigger function if the function itself is rolled back because of an error (eg, for a unique index error)? For example: create table tab1 (col1 int unique); create table tab2 (col1 int); CREATE OR REPLACE FUNCTION f_func1 () RETURNS trigger AS $$ BEGIN -- ... some processing ... INSERT INTO tab2 (col1) VALUES (new.col1); -- COMMIT the above statement, irrespective of whether this -- trigger/function is rolled back or not. RETURN new; end; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER tiu_t1 BEFORE UPDATE OR INSERT ON tab1 FOR EACH ROW EXECUTE PROCEDURE f_func1(); * * * Now, if you: insert into tab1 (col1) values (1); and tab1 already has a row with col1=1, tab2 must be updated even when the statement fails with: ERROR: duplicate key violates unique constraint... I know this can be achieved outside the DB (ie, with checks, etc), but I'd like to keep this aspect inside the DB. Also, I could perform selects inside the trigger to pre-empt a unique constraint error, but this will slow the inserts down. I can't wrap BEGIN/COMMIT around the INSERT in the trigger. Is there another way of achieving this? Any suggestions are appreciated. Regards ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings