Hi,
I’m running PostgreSQL 8.3.3 and I’m having trouble with triggers not always working. I have the following tables and functions as documented below. My problem is that if I perform an update on the Entity table and modify the Code field, why doesn’t the trigger for the Entity table execute? (Row was initially added via the Account table.)
Dale.
CREATE TABLE "Entity"
(
"ID" bigserial NOT NULL,
"Code" character varying(20) NOT NULL,
"Name" character varying(50) NOT NULL,
"Modified" timestamp(1) with time zone NOT NULL DEFAULT session_timestamp(),
"ModifiedBy" bigint DEFAULT userid(),
CONSTRAINT "pkEntity" PRIMARY KEY ("ID")
);
CREATE TABLE "Account"
(
"Balance" money NOT NULL DEFAULT '$0.00'::money,
CONSTRAINT "pkAccount" PRIMARY KEY ("ID"),
CONSTRAINT "uniqAccountCode" UNIQUE ("Code")
)
INHERITS ("Entity");
CREATE OR REPLACE FUNCTION entitytrigger() RETURNS trigger AS
$BODY$BEGIN
--Update modified details
raise notice '% being called for % of %.', TG_NAME, TG_OP, TG_TABLE_NAME;
new."Modified" := Session_TimeStamp();
new."ModifiedBy" := UserID();
return new;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER "trEntityUpdate"
BEFORE UPDATE
ON "Entity"
FOR EACH ROW
EXECUTE PROCEDURE entitytrigger();
CREATE TRIGGER "trAccountUpdate"
BEFORE UPDATE
ON "Account"
FOR EACH ROW
EXECUTE PROCEDURE entitytrigger();