Re: trigger and updates

From: cptkirkh <khill_at_scic.com>
Date: 29 Jan 2007 15:29:15 -0800
Message-ID: <1170113355.292554.44220@j27g2000cwj.googlegroups.com>

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-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.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; Received on Mon Jan 29 2007 - 17:29:15 CST