INSERT from trigger not happening?

Question

I'm new to triggers in PostgreSQL.
I have 3 tables in this example table1, table2 and table3.

The trigger fires on new record on table1.
The trigger function loops through all records of table2 that have the same product_id as the new record from table1.
It inserts into table3 some mixed values from the first 2 tables.
After that, the same INSERT query is inserted into a test table for testing purpose.

The problem is the INSERT on table3 is not happening. The INSERT on test table is fine and the logged insert query can be executed without troubles, so I don't know why it's not executed inside the trigger/function.

CREATE OR REPLACE FUNCTION my_trigger() RETURNS TRIGGER AS $my_trigger$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT t2.id_t2, t2.name_1, t2.name_2, t2.name_3 FROM table2 t2 WHERE t2.product_id=NEW.product_id 
  LOOP
    EXECUTE 'INSERT INTO table3 (id_t3, id_t1, name_1, name_2, name_3, bool_t2) VALUES (' || r.id_t2 || ',' || NEW.id_t1 || ', ''' || r.name_1 || ''',''' || r.name_2 || ''',''' || r.name_3 || ''', TRUE);';
    INSERT INTO test (field1, field2) VALUES(r.id_t2, 'INSERT INTO table3 (id_t3, id_t1, name_1, name_2, name_3, bool_t2) VALUES (' || r.id_t2 || ',' || NEW.id_t1 || ', ''' || r.name_1 || ''',''' || r.name_2 || ''',''' || r.name_3 || ''', TRUE);');
  END LOOP;
RETURN NEW;
END;
$my_trigger$ LANGUAGE plpgsql;

Edit: As @Rachcha asked, the trigger itself is defined this way:

CREATE TRIGGER my_trigger
  AFTER INSERT
  ON table1
  FOR EACH ROW
  EXECUTE PROCEDURE my_trigger();

Edit 2: I've also tried to insert without EXECUTE, but the result is the same: no errors but no fields inserted into table3.

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2013-03-19T16:47:19

Simpler function

It makes no sense to RETURN NEW in an AFTER trigger. I use RETURN NULL instead. Quoting the manual here:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.
No point in using a LOOP. Use a simple SQL statement instead.
No point in using dynamic SQL (EXECUTE).

CREATE OR REPLACE FUNCTION my_trigger()
  RETURNS TRIGGER AS
$my_trigger$
BEGIN

INSERT INTO table3 (id_t3, id_t1, name_1, name_2, name_3, bool_t2)
SELECT t2.id_t2, NEW.id_t1, t2.name_1, t2.name_2, t2.name_3, TRUE
FROM   table2 t2
WHERE  t2.product_id = NEW.product_id;

INSERT INTO test (field1, field2)
SELECT t2.id_t2, '??un_known??'
FROM   table2 t2
WHERE  t2.product_id = NEW.product_id;

RETURN NULL;

END
$my_trigger$ LANGUAGE plpgsql;

This should be working! -> SQLfiddle demo.

Debugging

If it doesn't work, the problem is something that is not in your question. Do you have any other triggers or rules defined on table1 or table test?

To debug, add this line to your trigger function to see if you even get there and what are values in NEW

RAISE EXCEPTION 'Values in NEW: %', NEW::text;

INSERT from trigger not happening?

1 Answers

Simpler function

Debugging