Thread: Trigger problem

HI all,

I have 2 tables and 1 trigger,

So, if i do

insert into abreviations(smot,mot,abreviation,pays) select
smot,mot,abreviation,pays from a;

the Trigger is executed for each row ONLY after all rows have been
inserted, so the
"   id    := currval(''abr_id'');"
in my trigger have false value (always the last value after 53000 insert
...)

Any way to fire a trigger really when a row is inserted ?

thx
----------------------------------------
create sequence abr_id;

create table a(
   smot        varchar(40),
   mot         varchar(40),
   abreviation varchar(40),
   ligne       varchar(8),
   pays        varchar(40)
);

create table abreviations (
   id          int   PRIMARY KEY DEFAULT nextval('abr_id'),
   smot        varchar(40),
   mot         varchar(40),
   abreviation varchar(40),
   date_       date,
   pays        varchar(40),
   comment     text
);
create table pays1(
   id          integer NOT NULL references abreviations(id) on delete
cascade on update cascade,
   pays varchar(3)
);
create table pays2(
   id          integer NOT NULL references abreviations(id) on delete
cascade on update cascade,
   pays varchar(3)
);
create table pays3(
   id          integer NOT NULL references abreviations(id) on delete
cascade on update cascade,
   pays varchar(3)
);
drop function AB_PAYS();
CREATE FUNCTION AB_PAYS() RETURNS OPAQUE AS '
DECLARE
   tpays1 char(3);
   tpays2 char(3);
   tpays3 char(3);
   id    int;
BEGIN
   id    := currval(''abr_id'');
   tpays1 := substring(new.pays from 1 for 3);
   tpays2 := substring(new.pays from 5 for 3);
   tpays3 := substring(new.pays from 9 for 3);

   if tpays1 <> '''' then
     insert into pays1 values(id,tpays1);
   end if;
   if tpays2 <> '''' then
     insert into pays2 values(id,tpays2);
   end if;
   if tpays3 <> '''' then
     insert into pays3 values(id,tpays3);
   end if;
   RETURN NEW;
END;
' LANGUAGE 'plpgsql';

drop trigger TAB_PAYS ON abreviations;
CREATE TRIGGER TAB_PAYS AFTER INSERT OR UPDATE ON abreviations
   FOR EACH ROW EXECUTE PROCEDURE AB_PAYS();

On Thu, 6 Dec 2001, mordicus wrote:

> So, if i do
>
> insert into abreviations(smot,mot,abreviation,pays) select
> smot,mot,abreviation,pays from a;
>
> the Trigger is executed for each row ONLY after all rows have been
> inserted, so the
> "   id    := currval(''abr_id'');"
> in my trigger have false value (always the last value after 53000 insert
> ...)

Wouldn't NEW.id give you the value you want rather than mucking with the
sequence value?

> Any way to fire a trigger really when a row is inserted ?

I don't think you can get the triggers to run such that currval would give
you what you want, but I could be wrong.

Stephan Szabo wrote:

> On Thu, 6 Dec 2001, mordicus wrote:
>
>> So, if i do
>>
>> insert into abreviations(smot,mot,abreviation,pays) select
>> smot,mot,abreviation,pays from a;
>>
>> the Trigger is executed for each row ONLY after all rows have been
>> inserted, so the
>> "   id    := currval(''abr_id'');"
>> in my trigger have false value (always the last value after 53000 insert
>> ...)
>
> Wouldn't NEW.id give you the value you want rather than mucking with the
> sequence value?
>

hum, not tested but i don't think because nextval('abr_id') is the default
value of id.

mordicus <mordicus@free.fr> writes:
> Stephan Szabo wrote:
>> Wouldn't NEW.id give you the value you want rather than mucking with the
>> sequence value?

> hum, not tested but i don't think because nextval('abr_id') is the default
> value of id.

Stephan gave you the correct answer.  A trigger runs after the values
that are to be inserted into the tuple have all been computed.

You might also consider whether a BEFORE trigger would make more sense
than an AFTER trigger, if you don't want the trigger execution delayed
till end-of-statement.

            regards, tom lane

mordicus wrote:
> Stephan Szabo wrote:
>
> > On Thu, 6 Dec 2001, mordicus wrote:
> >
> >> So, if i do
> >>
> >> insert into abreviations(smot,mot,abreviation,pays) select
> >> smot,mot,abreviation,pays from a;
> >>
> >> the Trigger is executed for each row ONLY after all rows have been
> >> inserted, so the
> >> "   id    := currval(''abr_id'');"
> >> in my trigger have false value (always the last value after 53000 insert
> >> ...)
> >
> > Wouldn't NEW.id give you the value you want rather than mucking with the
> > sequence value?
> >
>
> hum, not tested but i don't think because nextval('abr_id') is the default
> value of id.

    So what value do you want to have? If it is the value the NEW
    row has gotten when  it  was  inserted,  you'll  find  it  in
    NEW.id.  The  trigger  call  mechanism  doesn't know any more
    where that  value  was  coming  from,  it  simply  pulls  the
    inserted row and fires the trigger.


Jan

--

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



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com