Horton

Thread

  1. trigger question

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-27T12:31:37Z

    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

  2. Re: trigger question

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-27T14:33:10Z

    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

  3. Re: trigger question

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-27T15:19:34Z

    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

  4. Re: trigger question

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-27T15:27:19Z

    mikeo <mikeo@spectrumtelecorp.com> writes:
> 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'))

Hmm.  It sounds to me like Oracle treats the OLD fields as being NULL
if the context is INSERT, which is something we could certainly do at
the price of losing some error detection capability --- ie, if that
really had been a typo as I first thought, the system wouldn't flag it
for you.

Not sure which way is better.  Comments anyone?

			regards, tom lane

  5. Re: trigger question

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-27T15:50:09Z

    At 11:27 AM 6/27/00 -0400, Tom Lane wrote:
>mikeo <mikeo@spectrumtelecorp.com> writes:
>> 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'))
>
>Hmm.  It sounds to me like Oracle treats the OLD fields as being NULL
>if the context is INSERT, which is something we could certainly do at
>the price of losing some error detection capability --- ie, if that
>really had been a typo as I first thought, the system wouldn't flag it
>for you.
>
>Not sure which way is better.  Comments anyone?
>
>			regards, tom lane
>

it would make the insert or update trigger more flexible, and ,
truly by an insert or update procedure, IMHO :), but is definitely not 
a priority.  creating a rule for each is just as quick as writing a 
function with a trigger to call it.

mikeo

  6. Re: trigger question

    Anatoly K. Lasareff <tolik@aaanet.ru> — 2000-06-28T07:42:05Z

    >>>>> "m" == mikeo  <mikeo@spectrumtelecorp.com> writes:

 m> At 11:27 AM 6/27/00 -0400, Tom Lane wrote:
 >> mikeo <mikeo@spectrumtelecorp.com> writes:
 >>> 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'))
 >> 
 >> Hmm.  It sounds to me like Oracle treats the OLD fields as being NULL
 >> if the context is INSERT, which is something we could certainly do at
 >> the price of losing some error detection capability --- ie, if that
 >> really had been a typo as I first thought, the system wouldn't flag it
 >> for you.
 >> 
 >> Not sure which way is better.  Comments anyone?
 >> 
 >> regards, tom lane
 >> 

 m> it would make the insert or update trigger more flexible, and ,
 m> truly by an insert or update procedure, IMHO :), but is definitely not 
 m> a priority.  creating a rule for each is just as quick as writing a 
 m> function with a trigger to call it.

I suppose you have to use TG_OP variable in your triggers. Here is a
bit from documentation:

 ------
TG_OP

    Datatype text; a string of 'INSERT', 'UPDATE' or 'DELETE' telling for which operation the
    trigger is actually fired. 
 -----

So, you can type something about

  if TG_OP <> 'UPDATE' then
    ... ops for insert only
  else
    ... ops for update only
  end if;

  ... ops for both of them

-- 
Anatoly K. Lasareff              Email:       tolik@aaanet.ru