[GENERAL] Triggers not working

[Dale Harris]

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();