Re: Trigger definition . . . puzzled - Mailing list pgsql-sql

On Dec 12, 2007, at 3:38 PM, Rolf A. de By wrote:

> 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.

The function you've shown won't do anything because BEFORE row
triggers that return NULL don't do anything (for that row).  If you
want the operation to continue without any modification then just
return NEW.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com