Re: trigger question

>>>>> "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