Re: Help with Database Trigger

In article <1995Jun6.103544.1_at_corp00.d51.lilly.com>, yxfindat_at_corp00.d51.lilly.com says:
>
>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.
>>

 <snip>
>> When I tried to create the above trigger, I received error message saying
>> "field1" is not declared!

 <snip>
>
>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;
>end;
>
>

Just a semi-related aside to this...if you encounter this error on columns for tables other than the one for which the trigger was created, ensure that the owner of the trigger has the appropriate security granted to it for the other tables. ie. a post-insert trigger on TABLE1 which selects or updates or inserts on TABLE2 can get this error if the owner of the trigger doesn't have SELECT or UPDATE or INSERT authority granted to it for TABLE2. The error message doesn't really make that clear.

Doug Halke               | 
Cleveland, Ohio          |    Some are wise, some are otherwise.