Re: trigger and updates

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 -- Received on Mon Jan 29 2007 - 12:40:23 CST