Re: trigger for indexfield

Hi Michael,

Michael Naegler wrote:
>
> 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***

Oracle's triggers are by default fired once per DML statement not once per row.
In this case there is no _new or :old row.

You get wat you want if you insert a "FOR EACH ROW" clause in your trigger head.

--
uwe.schneider_at_xlink.net Received on Tue Nov 09 1999 - 04:57:48 CST