Re: a trigger question

Zhou, Lixin wrote:
> Thank all who answered and helped!
>
> Here is what I learnt so far:
>
> 1) Fire the trigger AFTER INSERT.  BEFORE INSERT won't work.
> 2) Deferrable does not seem to be important.

    1) Fire the trigger BEFORE INSERT does work
    2) If you make the constraint deferred
    3) And let the trigger return NEW instead of NULL;

> I've tested above, it does work as expected for PK/FKs that are integers.

    It also works for all other types of keys.

        create table first_tbl(
            v varchar(20) not null default 'abc',
            s text,
            primary key (v)
        );

        create table second_tbl(
            v varchar(20) not null default 'abc',
            s text,
            primary key (v),
            foreign key (v) references first_tbl (v) initially deferred
        );

        create function init_second_tbl() returns opaque as '
        declare
        begin
            insert into second_tbl (v) values (new.v);
            -- 1. this is wrong!
            -- insert into second_tbl (v) values (quote_literal(new.v));
            return new;
        end;
        ' language 'plpgsql';

        create trigger t_init_second_tbl
            before insert
            on first_tbl
            for each row
            execute procedure init_second_tbl();

        insert into first_tbl (v, s) values ('v', 's');

    Works  as  expected  with v7.2.1.  So what did you do? Let me
    guess, you  specified  the  constraint  DEFERRABLE  and  then
    forgot  to  actually put it into deferred mode, right?  Well,
    specifying it INITIALLY DEFERRED does the trick.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #