Inserting into Postgres within a trigger function

Question

I have a trigger function that I am running BEFORE INSERT on table1. In this function I do the following:

INSERT INTO table2 VALUES(x, y, z);
INSERT INTO table3 VALUES(x, a);

For some reason, these INSERT statements do not run. I know that the function is being called.

UPDATE:

More information, my trigger looks like this:

CREATE TRIGGER update_db
BEFORE INSERT ON table1
FOR EACH ROW
EXECUTE PROCEDURE update_all_db();

My procedure function doesn't do much. All I'm trying to do are a few statements as above.

Thank you in advance.

You need to provide a lot more information. Start with your trigger function, trigger, table definition, error messages, Postgres version and most importantly information about any other trigger involved. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 21, 2013 at 18:21
I have asked for substantially more than the scarce information you added. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 21, 2013 at 18:29
There are no error messages, this is Postgres 9.2. There are no other triggers. These are quite simply 3 arbitrary tables. — HappyCry
– HappyCry, Commented May 21, 2013 at 18:37
Try simplest mockup. Create another trivial table without any triggers etc and try to insert there. — Timur Sadykov
– Timur Sadykov, Commented May 21, 2013 at 18:45
Please show the actual trigger code from the function update_all_db() — user330315
– user330315, Commented May 21, 2013 at 18:52

Erwin Brandstetter · Accepted Answer · 2013-05-21 18:57:11Z

1

Semicolons (;) are missing behind your statements.

And it is a suicidal move not to include a column list for your table in a persisted INSERT statement. If you change the table definition of table2 or table3 in the future, the trigger silently breaks or destroys data in the worst case. Should be:

INSERT INTO table2(col1, col2, col3) VALUES(x, y, z);
INSERT INTO table3(col1, col2) VALUES(x, a);

But that's no reason the statement should just "not run".
This problem is due to something that is not in your question.

edited May 21, 2013 at 18:57

answered May 21, 2013 at 18:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HappyCry Over a year ago

Sorry, I missed the semi-colons in the questions. I'll update the question with more details.

Collectives™ on Stack Overflow

Inserting into Postgres within a trigger function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related