Re: Triggers on Oracle7 - newbie

"Keith" <keith.wilby_at_AwayWithYerCrap.com> wrote in message news:Xns96216D64CAF11keithwilby_at_10.15.188.42...
> Hi. This is my first posting to an Oracle forum, please be patient with
> me,
> I'm a clueless newbie in Oracle.
>
> I want to create a trigger that updates a field when the contents of
> another field changes. I've tried two approaches but they both won't
> compile:
>
> create or replace trigger t_oms_arr_af
>
> before insert or update
> of ver_arr_status
> on oms_version_tbl
> begin
> if ver_arr_status = 'PASSED' then
> update oms_version_tbl
> set ver_arr_act_fin = sysdate;
> end if;
>
> end t_oms_arr_af;
> /
> show errors
>
> 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;
> /
> show errors
>
> The second one gives the error "NEW or OLD references not allowed in table
> level triggers". I'm using Oracle 7.
>
> Thanks.
> Keith.

first, why version 7? it's way obsolete

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; Received on Tue Mar 22 2005 - 05:21:27 CST