Skip site navigation (1) Skip section navigation (2)

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

From: "Plugge, Joe R(dot)" <JRPlugge(at)west(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: DELETE and UPDATE triggers on parent table of partioned table not firing.
Date: 2012-05-03 16:40:59
Message-ID: BD69807DAE0CE44CA00A8338D0FDD083033A35B01A@oma00cexmbx03.corp.westworlds.com (view raw or flat)
Thread:
Lists: pgsql-admin
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?

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

Responses

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-05-03 17:54:25
Subject: Re: DELETE and UPDATE triggers on parent table of partioned table not firing.
Previous:From: Albe LaurenzDate: 2012-05-03 14:06:05
Subject: Re: Very long "<IDLE> in transaction" query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group