Re: trigger and updates

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 29 Jan 2007 11:13:51 -0800
Message-ID: <1170098027.246534@bubbleator.drizzle.com>

Mark D Powell wrote:

> 
> On Jan 29, 12:35 pm, "cptkirkh" <k..._at_scic.com> wrote:

>> I want to fill a table from a triggered event on another table. The
>> field that will be changed in the destination table will be determined
>> by what is changed on the source. I think I have set this up right
>> but I get an ORA-1733 error on the word "field" in the update
>> statement. It says virtual column not allowed here. How can I
>> resolve this or what does anybody suggest is a better way to work
>> aorund this problem. Or of course is my PL/SQL wrong? Thnaks for
>> your help. Here is the PL.SQL
>>
>> FIELD VARCHAR2(20);
>> x number;
>> y number;
>> old date;
>> new date;
>> id varchar2(10);
>>
>> BEGIN
>> FIELD := CASE
>> WHEN x = 1 AND y = 1 THEN 'CLI_CIC_NPDATE'
>>
>> ELSE 'NOT SURE'
>> END;
>>
>> DBMS_OUTPUT.PUT_LINE(FIELD);
>>
>> if nvl(old,'01-JAN-1901') <> nvl(new,'01-JAN-1901') and field is not
>> null then
>>
>> UPDATE DESIGNATION_INFO_TEST SET FIELD = sysdate WHERE
>> CLI_RID = id;
>> ELSE
>> INSERT INTO DESIGNATION_INFO_TEST(CLI_RID,FIELD)
>> VALUES (id,sysdate);
>> end if;
>> end;

> 
> You have a pl/sql variable named FIELD and a table column FIELD.  It 
> would appear that the parser is having difficulty telling which one 
> you are trying to reference.  You can find the variable scope rules 
> defined in the PL/SQL manual for your version.
> 
> I suggest you prefix or suffix your pl/sql variable names so that the 
> pl/sql variable names and table column names are not identical.
> 
> HTH -- Mark D Powell --

It is worse than that:

The following are reserved words:
FIELD
ID
NEW
OLD and '01-JAN-1901' is not a date ... it is a string and the OP is relying on implicit conversion which is best practice either.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jan 29 2007 - 13:13:51 CST