Re: locking question when using before update trigger
From: Tim X <timx_at_spamto.devnul.com>
Date: 29 Jan 2003 17:49:16 +1100
Message-ID: <87d6mgtpqr.fsf_at_tiger.rapttech.com.au>
Date: 29 Jan 2003 17:49:16 +1100
Message-ID: <87d6mgtpqr.fsf_at_tiger.rapttech.com.au>
>>>>> "Tim" == Tim Callaghan <timc_at_channel1.com> writes:
Tim> create or replace trigger im_ins_upd_table_a BEFORE INSERT OR Tim> UPDATE ON table_a FOR EACH ROW BEGIN Tim> if not ((:new.replicated_flag = 'Y') and (:old.replicated_flag Tim> = Tim> 'N')) THEN Tim> :new.last_touch_date := sysdate; Tim> END IF; Tim> END;
I'm afraid I don't know why yo would be seeing that blocking problem, but I do think you should modify your trigger to include an
if inserting then
....
elsif updating
...
end if;
block because your test uses an :old value and there are no :old values when doing an insert, only when doing an update. This would mean your AND test will always fail, which is probably what you want (e.g. sets the timestamp) when doing an insert, but it seems to me thats happening more as an accidental side effect rather than being explicit in your code.
Tim
-- Tim Cross The e-mail address on this message is FALSE (obviously!). My real e-mail is to a company in Australia called rapttech and my login is tcross - if you really need to send mail, you should be able to work it out!Received on Wed Jan 29 2003 - 07:49:16 CET