Reserved word is a column name - how to reference it in a trigger

From: Walt <walt_askier_at_yahoo.com>
Date: Wed, 18 Sep 2013 15:47:46 -0400
Message-ID: <Drn_t.49685$Rr1.42094_at_en-nntp-08.dc1.easynews.com>


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
Received on Wed Sep 18 2013 - 21:47:46 CEST

Original text of this message