Message-ID: <476054C8.80001@itc.nl>
Date: Wed, 12 Dec 2007 22:38:16 +0100
From: "Rolf A. de By" <deby@itc.nl>
Organization: ITC
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Trigger definition . . .  puzzled
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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