From: | tolik(at)aaanet(dot)ru (Anatoly K(dot) Lasareff) |
---|---|
To: | mikeo <mikeo(at)spectrumtelecorp(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: trigger question |
Date: | 2000-06-28 07:42:05 |
Message-ID: | 87g0pyl0aa.fsf@tolikus.hq.aaanet.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "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
From | Date | Subject | |
---|---|---|---|
Next Message | kurt miller | 2000-06-28 11:18:36 | Interface Question |
Previous Message | John Morton | 2000-06-28 07:39:58 | Restricting queries by the presence of a WHERE clause |