trigger for indexfield

Hi,

in my application the User should be able to seek case-insensetive. For large tables i insert an Index on secial Fields. My Problem was that the index dosent support funktions like upper() or lower() (Oracle 8.0.4.). So i insert a new field wich contains the same Value as the Field the use see, but is is upper().

I try to create a trigger wich update an Indexfield in my table.

***Trigger***

CREATE OR REPLACE TRIGGER "<table>"."<trigger_name>" BEFORE INSERT OR UPDATE
OF <Field> ON <Usr>.<table>

BEGIN
update <table> set <table>.<Field_IDX> = upper(:NEW.<Field>) where <table>.<UID> = :NEW.<UID>;
END;
***Trigger***

If i want to create Oracle show the following error.

***Error***

ORA-04082: NEW or OLD references not allowed in table level triggers
***Error***

Maybe anyone can send an exaplme to explain what i have to do.

I am a Oracle beginer!!!!!!!!!!!!!!!!!

Tschau

    Mischa Received on Tue Nov 09 1999 - 04:05:16 CST