Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.

CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE FUNCTION insert_twitter_interactions ...;
CREATE TRIGGER insert_twitter_interactions_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

CREATE OR REPLACE FUNCTION maintain_data_pointers ...;
CREATE TRIGGER maintain_data_pointers_trig
  BEFORE INSERT OR UPDATE on twitter_interactions
  FOR EACH ROW EXECUTE PROCEDURE insert_twitter_interactions();

I haven't fully verified, but I suspect the partitioning logic runs before the maintain trigger, and since the row doesn't end up in the parent table, then the 2nd trigger never fires.

What happens if I want to run an AFTER INSERT OR UPDATE as well? Since the row doesn't make it into the original table, then I'm at a loss to implement the after logic.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

I couldn't say it better than the manual does here:

If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

Neither the (alphabetically) later triggers on the same event nor any AFTER triggers will fire, if the BEFORE trigger cancels.

share|improve this answer
    
Good old RTFM... But reading the statement, if the insert trigger is last, then other triggers can fire and complete. I'll investigate this further. Thanks! –  François Beausoleil Feb 10 '12 at 3:30
    
@FrançoisBeausoleil: That's right. You can squeeze in other triggers on the same event before you cancel the INSERT with RETURN NULL. You could also put more program code into the same trigger function. –  Erwin Brandstetter Feb 10 '12 at 17:17

PostgreSQL executes the triggers in alphabetical order by name. So make sure to use names that get them in the order you want.

BTW your SQL creates the same trigger twice.

share|improve this answer
    
Simple copy paste error. Wrote the SQL above for illustrative purposes. Thanks for the answer! –  François Beausoleil Feb 9 '12 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.