Thread: Triggers not working
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();
"Dale Harris" <itsupport@jonkers.com.au> writes:
> 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.)
Worksforme ...
regression=# insert into "Account" values(1,'code','name');
INSERT 0 1
regression=# select * from "Entity";
ID | Code | Name | Modified | ModifiedBy
----+------+------+---------------------------+------------
1 | code | name | 2008-09-22 08:19:51.70-04 | postgres
(1 row)
regression=# update "Entity" set "Code" = 'foo' where "ID" = 1;
NOTICE: trAccountUpdate being called for UPDATE of Account.
UPDATE 1
regression=# select * from "Entity";
ID | Code | Name | Modified | ModifiedBy
----+------+------+---------------------------+------------
1 | foo | name | 2008-09-22 08:20:18.10-04 | postgres
(1 row)
regards, tom lane
Hi Tom,
The trigger trAccountUpdate got called, but why didn't the trigger
trEntityUpdate get called?
Regards,
Dale Harris.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, 22 September 2008 22:22
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Triggers not working
"Dale Harris" <itsupport@jonkers.com.au> writes:
> 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.)
Worksforme ...
regression=# insert into "Account" values(1,'code','name');
INSERT 0 1
regression=# select * from "Entity";
ID | Code | Name | Modified | ModifiedBy
----+------+------+---------------------------+------------
1 | code | name | 2008-09-22 08:19:51.70-04 | postgres
(1 row)
regression=# update "Entity" set "Code" = 'foo' where "ID" = 1;
NOTICE: trAccountUpdate being called for UPDATE of Account.
UPDATE 1
regression=# select * from "Entity";
ID | Code | Name | Modified | ModifiedBy
----+------+------+---------------------------+------------
1 | foo | name | 2008-09-22 08:20:18.10-04 | postgres
(1 row)
regards, tom lane
"Dale Harris" <itsupport@jonkers.com.au> writes:
> The trigger trAccountUpdate got called, but why didn't the trigger
> trEntityUpdate get called?
Triggers only apply to the exact table they're declared on, not
to child tables.
It does seem like there might be some use-case for applying a trigger to
child tables too, but that's not how it works now.
regards, tom lane
I would have called the Entity table the parent table and not the child
table as the Account table inherits from Entity. Therefore it appears that
the trigger only works on the table where the actual row was added/belongs
to.
It would be great if triggers on the parent table would work for any row
that appears there, even rows added via a child table. It would mean that
any new table that inherits the parent table, in my case Entity, would
automatically have the global trigger I want enforced. Nice enhancement :)
Regards,
Dale Harris.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, 23 September 2008 13:43
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Triggers not working
"Dale Harris" <itsupport@jonkers.com.au> writes:
> The trigger trAccountUpdate got called, but why didn't the trigger
> trEntityUpdate get called?
Triggers only apply to the exact table they're declared on, not
to child tables.
It does seem like there might be some use-case for applying a trigger to
child tables too, but that's not how it works now.
regards, tom lane