Trigger definition . . . puzzled
От | Rolf A. de By |
---|---|
Тема | Trigger definition . . . puzzled |
Дата | |
Msg-id | 476054C8.80001@itc.nl обсуждение исходный текст |
Ответы |
Re: Trigger definition . . . puzzled
|
Список | pgsql-sql |
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 identifiergid integer NOT NULL, -- The i identifierstatus character(1),confirmation character(1),CONSTRAINTpk_is PRIMARY KEY (sidx, gid) ) WITH (OIDS=FALSE); And the trigger definition is here: CREATE TRIGGER aw_archival_is2BEFORE UPDATE OR INSERT OR DELETEON i_sFOR EACH ROWEXECUTE PROCEDURE aw_archive_test(); And the trigger function: CREATE OR REPLACE FUNCTION aw_archive_test()RETURNS "trigger" AS $BODY$ BEGINRAISE WARNING 'Starting isa trigger for %', TG_OP;IF (TG_OP = 'UPDATE') THEN RAISE WARNING 'Calling insert_isa withupdate';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 INSERTWARNING: 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
В списке pgsql-sql по дате отправления: