Re: Triggers

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