Thread: Triggers
I have a with a a column defined as not null. The value however can be
found by looking it up in another table. I would like to create a
trigger that after insert would look up the need value and put it in the
record being inserted.
Unfortunately the column is defined as not null so I fear the insert
would fail and the trigger never get called. How can I get around this?
i.e.
create table t {
id serial primary key,
a integer not null,
b integer not null -- b can be found in another table
};
insert into t(a) values('1'); -- this would start the trigger and turn
the insert into:
insert into t(a,b) values('1', 'some value from another table');
Are thriggers the wrong way to go about this?
Jc
On Fri, 2002-08-16 at 14:42, Jean-Christian Imbeault wrote: > I have a with a a column defined as not null. The value however can be > found by looking it up in another table. I would like to create a > trigger that after insert would look up the need value and put it in the > record being inserted. > > Unfortunately the column is defined as not null so I fear the insert > would fail and the trigger never get called. How can I get around this? Surely you can use a BEFORE trigger, can't you? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "And whatsoever ye shall ask in my name, that will I do, that the Father may be glorified in the Son." John 14:13
Jean-Christian Imbeault wrote:
> I have a with a a column defined as not null. The value however can be
> found by looking it up in another table. I would like to create a
> trigger that after insert would look up the need value and put it in the
> record being inserted.
>
> Unfortunately the column is defined as not null so I fear the insert
> would fail and the trigger never get called. How can I get around this?
>
> create table t {
>
> id serial primary key,
> a integer not null,
> b integer not null -- b can be found in another table
> };
>
> insert into t(a) values('1'); -- this would start the trigger and turn
> the insert into:
>
> insert into t(a,b) values('1', 'some value from another table');
>
> Are thriggers the wrong way to go about this?
>
No, a trigger is indeed what you need - specifically a 'before insert'
trigger, which is run before the row is inserted, and gives you an
opportunity to alter the contents of the 'new' row before insertion.
e.g.
create function t_lookup_a() returns opaque as '
begin
new.b = 123; -- do whatever is needed here
return new;
end;
' language 'plpgsql';
create trigger t_insert before insert on t for each row
execute procedure t_lookup_a();
--
Peter Gibbs
EmKel Systems
Oliver Elphick <olly@lfix.co.uk> writes:
>> Unfortunately the column is defined as not null so I fear the insert
>> would fail and the trigger never get called. How can I get around this?
> Surely you can use a BEFORE trigger, can't you?
I think we check constraints (including NOT NULL) before firing
triggers.
The simple answer to this is not to use a constraint, but to rely on
insert and update triggers to substitute for a null (or throw an error)
in that column.
regards, tom lane
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
You can make the trigger fire before you do the insert and make sure you
get the value and then insert into the table.
Seems really odd that you would do it this way. I can't think why you
would not get the value first and then insert inot the table. I am
assuming you get the value via (a).
Anyway the first paragraph should give you the answer
HTH
On Fri, 16 Aug 2002, Jean-Christian Imbeault wrote:
> I have a with a a column defined as not null. The value however can be
> found by looking it up in another table. I would like to create a
> trigger that after insert would look up the need value and put it in the
> record being inserted.
>
> Unfortunately the column is defined as not null so I fear the insert
> would fail and the trigger never get called. How can I get around this?
>
> i.e.
>
> create table t {
>
> id serial primary key,
> a integer not null,
> b integer not null -- b can be found in another table
> };
>
> insert into t(a) values('1'); -- this would start the trigger and turn
> the insert into:
>
> insert into t(a,b) values('1', 'some value from another table');
>
> Are thriggers the wrong way to go about this?
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Darren Ferguson
Darren Ferguson wrote: > > Seems really odd that you would do it this way. I can't think why you > would not get the value first and then insert inot the table. I am > assuming you get the value via (a). I'm doing it this way for speed. I'm using PHP for the client and having the client look up a value is much slower than having a trigger look it up for me. At least I hope so ... Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> Tom Lane wrote:
>>> I think we check constraints (including NOT NULL) before firing
>>> triggers.
> Seems like the trigger gets fired before the constraint checking.
A quick look in execMain.c demonstrates that you are correct. I am not
sure why I thought otherwise --- maybe it was different a few releases
back?
regards, tom lane
On Fri, 2002-08-16 at 15:14, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > >> Unfortunately the column is defined as not null so I fear the insert > >> would fail and the trigger never get called. How can I get around this? > > > Surely you can use a BEFORE trigger, can't you? > > I think we check constraints (including NOT NULL) before firing > triggers. No, I just tried it (in 7.2.1). The BEFORE trigger successfully replaced a null, thus satisfying the constraint. > The simple answer to this is not to use a constraint, but to rely on > insert and update triggers to substitute for a null (or throw an error) > in that column. > > regards, tom lane -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "And whatsoever ye shall ask in my name, that will I do, that the Father may be glorified in the Son." John 14:13
On the subject of triggers, what would be required to allow perl to be a trigger language?
Thanks,
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
GB Clark <postgres@vsservices.com> writes:
> On the subject of triggers, what would be required to allow perl to be a trigger language?
A trigger interface. Just a small matter of programming... pltcl's
is about 300 lines of code, I imagine one for plperl would be of
similar size.
regards, tom lane
On Sun, 18 Aug 2002 16:29:34 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> GB Clark <postgres@vsservices.com> writes:
> > On the subject of triggers, what would be required to allow perl to be a trigger language?
>
> A trigger interface. Just a small matter of programming... pltcl's
> is about 300 lines of code, I imagine one for plperl would be of
> similar size.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
I'm running 7.2.1 here, should it be fairly easy to port to 7.3? Has the interface
changed?
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
On Sun, 18 Aug 2002 15:53:13 -0500
GB Clark <postgres@vsservices.com> wrote:
> On Sun, 18 Aug 2002 16:29:34 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > GB Clark <postgres@vsservices.com> writes:
> > > On the subject of triggers, what would be required to allow perl to be a trigger language?
> >
> > A trigger interface. Just a small matter of programming... pltcl's
> > is about 300 lines of code, I imagine one for plperl would be of
> > similar size.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
> I'm running 7.2.1 here, should it be fairly easy to port to 7.3? Has the interface
> changed?
>
> GB
Opps,
I know perl XS and C. But to get anywhere in here I would have to learn ALOT of Pg internals and I
just don't have the time right now.:(
Sorry for taking up your time folks.
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?