Re: trigger question

At 10:33 AM 6/27/00 -0400, Tom Lane wrote:
>mikeo <mikeo@spectrumtelecorp.com> writes:
>> CREATE function rates_hist_function()
>>         returns opaque
>>         as 'BEGIN
>>                if ( old.rt_valid <> ''P'' or new.rt_valid not in
(''Y'',''N''))
>                      ^^^^^^^^^^^^
>
>> 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?
>
>Because you referenced it in the function code.  Am I missing something?
>
>            regards, tom lane
>

maybe.
in oracle, the triggers were smart enough to know not to reference
an old value on insert in an "insert or update" trigger procedure,
apparently.

this is the original oracle trigger that works fine
with the same insert statement:

CREATE OR REPLACE TRIGGER rates_hist_trigger
        before insert or update on rates
        for each row
             WHEN (old.rt_valid <> 'P' or new.rt_valid not in ('Y','N'))
        begin
        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, sysdate, :new.rt_usoc_def_factor
                       );
end;
/

i can easily get around this using rules.  my main objective is to not have to
change too much code as we migrate over to postgres from oracle and that is
not
too much of a change.

thanks,
   mikeo