From: Rolf A. de By <deby@itc.nl>
To: pgsql-sql@postgresql.org
Subject: Trigger definition . . . puzzled
Date: Wed, 12 Dec 2007 22:38:16 +0100
Message-ID: <476054C8.80001@itc.nl> (raw)
Greetings list,
Running pg 8.2.3. on a windows machine, I have become blind in a
trigger definition puzzle, so hope that somebody may help me understand
where I goof.
I have a base table i_s that has three tables that inherit from it, one
of them being i_s_nowhere. The base table should be left empty, but I
want it to be the prime port of call for data changes. Hence the
following set-up. Let me try to be brief, in the hope of not leaving
out relevant detail.
Base table def is as follows:
CREATE TABLE i_s
(
sidx integer NOT NULL, -- The s identifier
gid integer NOT NULL, -- The i identifier
status character(1),
confirmation character(1),
CONSTRAINT pk_is PRIMARY KEY (sidx, gid)
)
WITH (OIDS=FALSE);
And the trigger definition is here:
CREATE TRIGGER aw_archival_is2
BEFORE UPDATE OR INSERT OR DELETE
ON i_s
FOR EACH ROW
EXECUTE PROCEDURE aw_archive_test();
And the trigger function:
CREATE OR REPLACE FUNCTION aw_archive_test()
RETURNS "trigger" AS
$BODY$
BEGIN
RAISE WARNING 'Starting isa trigger for %', TG_OP;
IF (TG_OP = 'UPDATE') THEN
RAISE WARNING 'Calling insert_isa with update';
ELSIF (TG_OP = 'INSERT') THEN
RAISE WARNING 'Calling insert_isa with insert';
ELSIF (TG_OP = 'DELETE') THEN
RAISE WARNING 'Calling insert_isa with delete';
END IF; RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
The trigger is enabled.
Yes, this does intentionally nothing. The real code will obviously take
care of data change in proper subtables. Well, the trigger should do
nothing now . . . What I cannot get round to understanding is that an
insert attempt will nicely give me two warnings, and will not insert, as
expected with this code:
WARNING: Starting isa trigger for INSERT
WARNING: Calling insert_isa with insert
Query returned successfully: 0 rows affected, 31 ms execution time.
But an attempt to update actually magically goes to the proper subtable
and performs the update:
Query returned successfully: 1 rows affected, 16 ms execution time.
Where did I deserve this?? ;-)
In attempts to solve this I did mess around with trigger and trigger
function definitions a bit. Could there be funny traces of this? What
is the best way to analyse this behavior? I am testing from a pgAdmin
1.8 setup.
All suggestions welcome!
--
Rolf A. de By
The Netherlands
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: deby@itc.nl
Subject: Re: Trigger definition . . . puzzled
In-Reply-To: <476054C8.80001@itc.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox