Re: Triggers

Tom Lane wrote:
 >
 > I think we check constraints (including NOT NULL) before firing
 > triggers.

Seems like the trigger gets fired before the constraint checking. I have
the following table and trigger and SQL that all seem to work:

create table TMP_LI (

cart_id                 integer         references TMP_CART(id),
li_id                   integer         not null,
shop_id                 integer         references CHARISMA_SHOPS(id),
prod_id                 char(12)        references PRODUCTS(id),
quantity                int2            not null,
price                   integer         not null,

primary key (cart_id, li_id)
);

create or replace function set_price() returns opaque as '
   declare
     row record;
   begin
     select into row sell_price from products where id=new.prod_id;
     new.price = row.sell_price;
     return new;
   end;
' language 'plpgsql';

create trigger insert_into_tmp_li before insert or update
   on tmp_li for each row
   execute procedure set_price();

insert into tmp_li(cart_id,li_id,shop_id,prod_id,quantity,price)
values('31','0','','289000101554','1')

Jc