Re: trigger and updates

On Jan 29, 6:29 pm, "cptkirkh" <k..._at_scic.com> wrote:
> On Jan 29, 2:28 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > cptkirkhwrote:
>
> > > 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-Hidequoted 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.Now that you've changed it ... repost it along with your version number
> > and the DDL for any tables.
> > --
> > 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 -DECLARE
> stuff VARCHAR2 (20);
> x NUMBER;
> y NUMBER;
> chl_old DATE;
> chl_new DATE;
> chl_id VARCHAR2 (10);
> BEGIN
> stuff := CASE
> WHEN x = 1 AND y = 1
> THEN 'CLI_CIC_NPDATE'
> ELSE 'NOT SURE'
> END;
> DBMS_OUTPUT.put_line (stuff);
>
> IF NVL (chl_old, '01-JAN-1901') <> NVL (chl_new, '01-JAN-1901')
> AND stuff IS NOT NULL
> THEN
> UPDATE designation_info_test
> SET stuff = SYSDATE
> WHERE cli_rid = chl_id;
> ELSE
> INSERT INTO designation_info_test
> (cli_rid, stuff
> )
> VALUES (ID, SYSDATE
> );
> END IF;
> END;- Hide quoted text -- Show quoted text -

What your are trying to do is change the column name being updated based on the values of an expression. To do that you need either to code each possible update and execute the correct via a CASE or IF- -ELSE structure or you have to use dynamic SQL. Dynamic SQL is provided via the execute immediate statement or the dbms_sql package.

v_string = 'update table set '||v_the_col_name||' where .....' execute immediate v_string;

See the pl/sql manual cause I typed this from memory and probably made a stupid mistake somewhere.

HTH -- Mark D Powell -- Received on Mon Jan 29 2007 - 18:16:54 CST