X-Received: by 10.66.240.203 with SMTP id wc11mr14186230pac.29.1379538670572; Wed, 18 Sep 2013 14:11:10 -0700 (PDT) X-Received: by 10.50.127.229 with SMTP id nj5mr1104948igb.2.1379538670200; Wed, 18 Sep 2013 14:11:10 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!87.79.20.101.MISMATCH!newsreader4.netcologne.de!news.netcologne.de!news.glorb.com!y3no32212205pbx.0!news-out.google.com!z6ni59055pbu.0!nntp.google.com!d5no449321qap.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Wed, 18 Sep 2013 14:11:09 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <3b05cb17-6775-4bf3-a10e-aead95610442@googlegroups.com> Subject: Re: Reserved word is a column name - how to reference it in a trigger From: ddf Injection-Date: Wed, 18 Sep 2013 21:11:10 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: news.cambrium.nl On Wednesday, September 18, 2013 1:47:46 PM UTC-6, Walt wrote: > We have a table that has a column named "TYPE". I know this is bad > > practice and after considerable time and effort we have renamed it as > > "INTERVIEW_TYPE" and updated all our applications. > > > > Except one, which hasn't been compiled in several years, and > > resurrecting it is not an easy task. As a temporary measure we'd like > > to just make a trigger to keep the old column in synch with the new one > > until we can find time to re-write or re-compile the old app. > > Unfortunately, the following code throws an error: > > > > > > CREATE OR REPLACE TRIGGER MY_TRIGGER > > BEFORE INSERT OR UPDATE > > ON MYTABLE > > REFERENCING NEW AS NEW OLD AS OLD > > FOR EACH ROW > > DECLARE > > tmpVar NUMBER; > > > > BEGIN > > :NEW.TYPE := :NEW.INTERVIEW_TYPE; > > END MY_TRIGGER; > > > > > > ERROR CODE: 24344 > > 29/4 PLS-00049: bad bind variable 'NEW.TYPE' > > > > > > > > Anybody know how to "escape" the word TYPE so that the plsql compiler > > interprets it as a column name instead of a keyword? Maybe do it by > > referencing the column number instead of name? > > > > I know, renaming the column and adapting all the apps is the real > > answer, but we'd like to just throw a trigger at it and buy some time > > for now. Thanks. > > > > > > > > -- > > //Walt The trigger cannot be created since the column is no longer named TYPE, meaning TYPE is no longer in the column block for the trigger to use. You will need to resurrect this old application and make the necessary code changes. David Fitzjarrell