Re: HELP with TRIGGER

In article <41gjli$lbb_at_grivel.une.edu.au> lukose_at_neumann.une.edu.au (Dickson Lukose) writes:

> I am very new to ORACLE. I need help with the following error
> that I am geeting when I run a "small" TRIGGER that I wrote the other day.
>
> What is wrong with the TRIGGER ?
>
> I have attached the SQL SCRIPT file, as well as the error that Iam getting when
> I attempt to delete tuples from relation a3.
>
>
> Any pointers or help will be most appreciated.
>
> CREATE TRIGGER a2Nulify
> BEFORE DELETE ON OPS$NEURAL.a3
> FOR EACH ROW
> BEGIN
> UPDATE OPS$NEURAL.a4
> set OPS$NEURAL.a4.CUSTOMER_NUMBER = NULL
> where a3.customer_number = a4.customer_number;

The problem is with the update statement in the PL/SQL block. a3.customer_number is not a valid column since it's not referenced in the statement itself. The proper (and more concise) way of doing this is:

	update OPS$NEURAL.a4
	set    customer_number = NULL
	where  customer_number = :old.customer_number;

--
 Alvin W. Law ........................................... Oracle Corporation
 Project Leader ............................. 300 Oracle Parkway, Box 659305
 Applications Design & Architecture ............... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ...... Voice: +1.415.506.8317 . Fax: +1.415.506.7294

******************************** W a r n i n g ******************************
Microsoft Network is prohibited from reproducing this work in any form, in
whole or in part, without the express written consent of the original author.