Re: trigger and updates

On Jan 29, 1:13 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -- Show quoted text -

Ok so I went in and changed the names to non-reserved words. still get the same error. In answer to Mark's statement I don't have two field columns. the column in the table that needs to be updated is determined on what item is changed in the source table for example if x and y both equal one then change the column cli_cic_npdate at the row equal to the cli_id. the column could change names. I just left out the other possiblities toi shorten what I posted. Received on Mon Jan 29 2007 - 13:58:15 CST