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