Uppercase all VARCHAR fields on Insert/Update Trigger?

[aljubicic]

Hi,

I want to be able to uppercase all Varchar2 fields on an Insert or Update Trigger. Currently I actually specify the the columns but can this be done generically across allthe fields without specifying the column names.

CREATE OR REPLACE TRIGGER TOWER.INGPTST_TBIU_MOD
BEFORE INSERT OR UPDATE
ON TOWER.INGPTST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

BEGIN

:NEW.MODDATE := SYSDATE;
:NEW.MODTIME := TO_CHAR(SYSDATE, 'HH24MISS');
:NEW.MODNAME := SUBSTR(USER, 1, 10);
:NEW.CUSTNAME := UPPERNEW.CUSTNAME);
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;
/

So for column CUSTNAME instead of specifying the name can it done by either looping through columns or specifying across the board for all columns.

Any examples would be great.

Thanks

 

[SantaMufasa]

Aljubicic,

Why not just explicitly list the columns you want to UPPER? There can't be so many that you could not have entered them while you were waiting for me to respond, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]