Re: Triggers on Oracle7 - newbie

From: Jim McMahon <jp_mcmahon_at_hotmail.com>
Date: Tue, 22 Mar 2005 12:02:54 GMT
Message-ID: <424005f7.87229433@nntp.charter.net>

Keith <keith.wilby_at_AwayWithYerCrap.com> wrote:

>"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
>
>> first, why version 7? it's way obsolete
>
>I know, but that's what I'm stuck with where I work.
>
>>
>> however, this issue is the same across database versions (starting with
>> 7)
>>
>> you need to create a row-level trigger -- check out the FOR EACH ROW
>> keywords in the SQL Reference manual
>>
>> typical example:
>>
>> create or replace trigger tasks_bir
>> before insert on tasks
>> for each row
>> begin
>> select tasks_id.nextval into :new.id from dual;
>> end;
>
>I did wonder about "FOR EACH ROW" but wouldn't that update all rows?
>Remember I'm a clueless newbie!
>
>Thanks for your help.
>Keith.

"FOR EACH ROW" will only affect the rows affected by the triggering statement. Presumably, you'll have a WHERE clause on the triggering statement that limits the rows affected. However, your second example *would* affect all rows because you're doing another UPDATE without a WHERE clause within your trigger. See below for an alternative.

YOUR ORIGINAL:
create or replace trigger t_oms_arr_af

	before insert or update
	of ver_arr_status
	on oms_version_tbl
begin
	if :new.ver_arr_status = 'PASSED' then
		update oms_version_tbl
		set ver_arr_act_fin = sysdate;   
	end if;

end t_oms_arr_af;

POSSIBLY DESIRED:
create or replace trigger t_oms_arr_af

	before insert or update
	of ver_arr_status
	on oms_version_tbl
        for each row
begin
	if :new.ver_arr_status = 'PASSED' then
		:new.oms_version_tbl = sysdate;   
	end if;

end t_oms_arr_af;

Notice that by not doing an UPDATE without a WHERE clause that rows unaffected by the triggering statement won't be touched. I'm assuming that's your intent. Hope this helps.

Being ordinary and nothing special is a full-time job. jp_mcmahon_at_hotmail.com (Jim McMahon in real life) Received on Tue Mar 22 2005 - 06:02:54 CST