Trigger problem - Mailing list pgsql-general

From mordicus
Subject Trigger problem
Date
Msg-id 9uokh2$qqc$1@news.tht.net
Whole thread Raw
Responses Re: Trigger problem
List pgsql-general
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();



pgsql-general by date:

Previous
From: "Gregory Wood"
Date:
Subject: Re: update returns 1, but no changes have been made
Next
From: Stephan Szabo
Date:
Subject: Re: Database links in Postgresql.