Re: trigger question

>>>>> "m" == mikeo <mikeo(at)spectrumtelecorp(dot)com> writes:

m> At 11:27 AM 6/27/00 -0400, Tom Lane wrote:
>> mikeo <mikeo(at)spectrumtelecorp(dot)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(at)aaanet(dot)ru