CODEWITHSUNDEEP
postgresqltriggers
shrikant sanap
shrikant sanap

Reputation: 23

postgresql trigger not working

i have a table "demand_details" on update or delete i want to store values of each row in another table "demand_details_log"

my functions is as follows

CREATE OR REPLACE FUNCTION water_blogb() RETURNS trigger AS
$BODY$ 
BEGIN 
    IF (TG_OP='UPDATE') THEN  
       INSERT INTO demand_details_log VALUES ('U',now(),OLD.*); 
       RETURN NEW; 
    END IF; 
    IF (TG_OP='DELETE') THEN  
       INSERT INTO demand_details_log VALUES ('D',now(),OLD.*); 
       RETURN OLD; 
    END IF;
END;
$BODY$ LANGUAGE plpgsql

my trigger is as follows

CREATE TRIGGER water_btrg_b
  AFTER UPDATE OR DELETE
  ON demand_details
  FOR EACH ROW
  EXECUTE PROCEDURE water_blogb();

MY problem is the same trigger and functions works well on other table (by changing table,trigger and function name) but not working with demand table. I tried with "RAISE NOTICE 'working...'" in both in other table trigger gets fired but in demand table its not fired at all.

Upvotes: 0

Views: 2502

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

As you found, triggers are not inherited. This leads to some difficulties in managing triggers in inherited table structures. You may want to read up on some of the issues involved at http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html and http://ledgersmbdev.blogspot.com/2012/08/or-modelling-32-setsubset-modelling.html.

Now those do not address table partitioning directly which may be what you are trying to do here. I would recommend that you build in some additional tests that you can run to check and make sure that triggers are properly installed on all subtables. I would suggest taking a look at How to find inherited tables programatically in PostgreSQL? and also the pg_trigger table so that you can build a report of child tables which do not share the triggers of their parents.

Upvotes: 0

Related Questions