CODEWITHSUNDEEP
databaseoracle-databasetriggersuppercase
miguemx
miguemx

Reputation: 51

Upper case before insert a row

I need to insert all my data in uppercase. I'm using an Oracle database, and I wrote a trigger which could do the work. The problem is that I don't know whether the :NEW can be override.

This is my code.

CREATE OR REPLACE TRIGGER my_trigger
    BEFORE INSERT OR UPDATE ON MY_TABLE
    FOR EACH ROW
        DECLARE
          CURSOR fields IS
                SELECT COLUMN_NAME FROM user_tab_columns WHERE table_name='MY_TABLE';
           field  user_tab_columns%ROWTYPE;
    BEGIN
       FOR field IN fields LOOP
            DECLARE
                theField VARCHAR2(100) := field.COLUMN_NAME;
                newValue NVARCHAR2(1000) := :NEW."MY_FIELD";
                BEGIN
                    :NEW."MY_FIELD" := UPPER(nValor);
                END;
        END LOOP;
    END;
/

That works with the field "MY_FIELD" but I need to use my variable "theField" instead. In that way I think that all the fields in the table can be replaced.

I hope someone can help me, or suggest me another way to do that.

Thank you.

Upvotes: 1

Views: 9504

Answers (1)

ozOracle
ozOracle

Reputation: 57

The way you want your code to work is defiantly inefficient. Imagine for each modified row, Oracle has to loop through all the columns in the table!

Write the trigger in the normal way, like the following

:NEW.FIRST_NAME = UPPER(:NEW.FIRST_NAME);
:NEW.LAST_NAME = UPPER(:NEW.LAST_NAME);

Now, you face the challenge of writing this to all the tables. For that I suggest writing PL/SQL code that generates the code for you. You give the table name, and the code generates the CREATE TRIGGER code for you. It could be something like this

FOR C IN ( SELECT COLUMN_NAME AS CNAME FROM user_tab_columns WHERE table_name='MY_TABLE')
 DBMS_OUTPUT.PUT_LINE(':NEW.'||CNAME || ':=UPPER(:NEW.'||CNAME ||')' );
END LOOP;

If you have too many columns and tables, the DBMS_OUT buffer may get full. I'd prefer to insert the code into a temporary table.

Upvotes: 2

Related Questions