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

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 Received on Wed Sep 18 2013 - 23:11:09 CEST