Re: Help with Database Trigger

In article <3qrhla$123_at_raffles.technet.sg>, shchua_at_technet.sg (SH Chua) writes:
> I want to create a database trigger to validate the values of some fields
> whenever there's any insert/update on table.
>
> Example :
>
> Table1 has 3 fields :
> field1 varchar2(5)
> field2 varchar2(5)
> field3 varchar2(5)
>
> The trigger I tried to create is as follows :
>
> create trigger
> after insert or update on table1
> begin
> if field1 is null
> then
> if field2 is not in ('A', 'B', 'C')
> then raise_application_err( .........);
> end if;
> else
> if field2 is null or field3 is not in ('X', 'Y')
> then raise_application_error(.............);
> end if;
> end;
>
>
> When I tried to create the above trigger, I received error message saying
> "field1" is not declared!
> "field1" is actually from the table TABLE1; why must I declare the field?
> How can I write the above trigger to validate the values of the field before
> user commits the record into the table?
> Prompt reply is appreciated.
>

You need to tell it you are referencing the columns value as it was before the update by preceding the columnsw with ':new.'

create trigger c
after insert or update on table1
begin

      if :new.field1 is null
      then
          if :new.field2 is not in ('A', 'B', 'C')
          then raise_application_err( .........);
          end if;
     else
          if :new.field2 is null or :new.field3 is not in ('X', 'Y')
          then raise_application_error(.............);
     end if;