trigger question

Поиск
Список
Период
Сортировка
От mikeo
Тема trigger question
Дата
Msg-id 3.0.1.32.20000627083137.009497f0@pop.spectrumtelecorp.com
обсуждение исходный текст
Ответы Re: trigger question
Список pgsql-general
hi,
  i've created a function as follows:

drop function rates_hist_function();
CREATE function rates_hist_function()
        returns opaque
        as 'BEGIN
               if ( old.rt_valid <> ''P'' or new.rt_valid not in
(''Y'',''N''))
                then
                  new.rt_timestamp = now();
                  insert into rates_hist values (
                        new.cut_id,new.ct_key,new.rtm_id,new.rt_sell_factor,

new.rt_sell_msg_cost,new.rt_sell_init_sec,new.rt_sell_init_cost,

new.rt_sell_addl_sec,new.rt_sell_addl_cost,new.rt_buy_factor,

new.rt_buy_msg_cost,new.rt_buy_init_sec,new.rt_buy_init_cost,

new.rt_buy_addl_sec,new.rt_buy_addl_cost,new.rt_valid,new.rse_id,
                        new.wu_id,new.rt_timestamp, new.rt_usoc_def_factor
                        );
               end if;
               return new;
            END;'
language 'plpgsql';

and i call it from this trigger:

drop TRIGGER rates_hist_trigger on rates;
CREATE TRIGGER rates_hist_trigger
        after insert or update on rates
        for each row
        execute procedure rates_hist_function();

when i attempt to insert a row into the rates table using this statement:

insert into rates(cut_id,ct_key,rt_valid,...,rt_timestamp,rt_usoc_def_factor)
          values ('mikeo',123456,'x',...,now(),1.35);

i get this error:
ERROR:  record old is unassigned yet

since this trigger is for both insert or update, why does it expect the "OLD"
value to already exist, as it would not for insert?  second, is there a way
to tell a function that it's inserting or updating, as in oracle's
"if updating..."?


thanks,
    mikeo

В списке pgsql-general по дате отправления:

Предыдущее
От: "Morten W. Petersen"
Дата:
Сообщение: Re: How to dump from Postgre
Следующее
От: "Dale Anderson"
Дата:
Сообщение: Re: DateTime fields