Thread: DELETE and UPDATE triggers on parent table of partioned table not firing.

Using postgres 9.0.7 on RHEL 5.4.  I have a parent table that is partitioned by day.  My inserts are working correctly
andare being directed to the correct child table.  I also have both an UPDATE and DELETE trigger on the parent table
thatare defined as AFTER triggers.  The actual update and delete operation works, however the triggers do not seem to
befiring.  What am I doing wrong? 

Parent table:

CREATE TABLE foo (
    unit integer NOT NULL,
    line smallint NOT NULL,
    system_connect_timestamp bigint NOT NULL,
    system_connect_datetime timestamp without time zone NOT NULL,
    random_seq bigint NOT NULL,
    sales_slot character varying(5),
    account character varying(20),
    batch_id integer
);


ALTER TABLE public.foo OWNER TO postgres;

ALTER TABLE ONLY foo ADD CONSTRAINT foo_pk PRIMARY KEY (unit, line, system_connect_timestamp, random_seq);

CREATE TRIGGER foo_del_trg AFTER DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE foo_del_audit_func();

CREATE TRIGGER foo_upd_trg AFTER UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE foo_upd_audit_func();

CREATE TRIGGER insert_foo_trigger BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE insert_foo_trigger();

REVOKE ALL ON TABLE foo FROM PUBLIC;
GRANT SELECT,INSERT,UPDATE ON TABLE foo TO public;

Audit table:

CREATE TABLE foo_audit (
    unit integer NOT NULL,
    line smallint NOT NULL,
    system_connect_timestamp bigint NOT NULL,
    system_connect_datetime timestamp without time zone NOT NULL,
    random_seq bigint NOT NULL,
    sales_slot character varying(5),
    account character varying(20),
    batch_id integer,
    change_type character(6),
    change_date timestamp(2) without time zone,
    change_user character varying(60)
) TABLESPACE comcastdata;


ALTER TABLE public.foo_audit OWNER TO postgres;

REVOKE ALL ON TABLE foo_audit FROM PUBLIC;
GRANT SELECT ON TABLE foo_audit TO PUBLIC;



Functions:

CREATE OR REPLACE FUNCTION foo_del_audit_func() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
            INSERT INTO foo_audit
(unit, line, system_connect_timestamp, system_connect_datetime, random_seq, sales_slot, account, batch_id,
change_type,change_date,change_user)
VALUES
(OLD.unit, OLD.line, OLD.system_connect_timestamp, OLD.system_connect_datetime, OLD.random_seq, OLD.sales_slot,
OLD.account,OLD.batch_id,'DELETE',CURRENT_TIMESTAMP,session_user::varchar(60)); 
                RETURN NULL; -- result is ignored since this is an AFTER trigger

    END;
$$;


ALTER FUNCTION public.foo_del_audit_func() OWNER TO postgres;


CREATE OR REPLACE FUNCTION foo_upd_audit_func() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
            INSERT INTO foo_audit
(unit, line, system_connect_timestamp, system_connect_datetime, random_seq, sales_slot, account, batch_id,
change_type,change_date,change_user)
VALUES
(OLD.unit, OLD.line, OLD.system_connect_timestamp, OLD.system_connect_datetime, OLD.random_seq, OLD.sales_slot,
OLD.account,OLD.batch_id,'UPDATE',CURRENT_TIMESTAMP,session_user::varchar(60)); 
            RETURN NULL; -- result is ignored since this is an AFTER trigger

    END;
$$;


ALTER FUNCTION public.foo_upd_audit_func() OWNER TO postgres;

Any suggestions would be greatly appreciated.


Joe plugge

"Plugge, Joe R." <JRPlugge@west.com> wrote:

> Using postgres 9.0.7 on RHEL 5.4.  I have a parent table that is
> partitioned by day.  My inserts are working correctly and are
> being directed to the correct child table.  I also have both an
> UPDATE and DELETE trigger on the parent table that are defined as
> AFTER triggers.  The actual update and delete operation works,
> however the triggers do not seem to be firing.  What am I doing
> wrong?

The DELETE and UPDATE triggers need to be on the child tables.  An
operation on a child doesn't fire the triggers of the parent.

-Kevin