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(at)Yahoo(dot)com #