1 person found this helpful
This isn't an APEX question, however.... you trigger is mutating because you are querying the same table that you are attempting to change the data in. There isn't an easy fix, if you really need to do this have a look at the AskTom site.
In your case, it looks like you just need to have a unique functional index based on upper(item_name).
1 person found this helpful
Please do not use capital letters to communicate. On the net, caps are used to represent "shouting". It doesn't read easy and screams "PAY THIS ATTENTION". When opening this thread the first reaction would be to simply back out, as it's not easy enough on the eye to take the time to read.
Similarly, you can put your actual code in a code-like block by using the syntax highlighting (Advanced editor, use the double blue arrow to get the option).
Keeping these things in mind will make for a much more appreciated post.
What Andy meant with unique index is: use a unique constraint to force uniqueness on the item name on the table. You do not need to use triggers and PLSQL to get this functionality since all you do is check this one column in your table for uniqueness, and that is what unique constraints are used for.
By "functional" is meant that by default a unique constraint will simply check the values of the column for uniqueness, capitalization included, which is not what you want. Therefor, you'd need to create a constraint which is just slightly advanced in that it would be based on a function performed on the column, this being UPPER().
With that constraint/index in place you won't need to have that trigger and plsql code to verify data integrity, as oracle will do it right away. You will then receive a unique constraint violation error in your application. (DUP_VAL_ON_INDEX)
An example would be:
CREATE UNIQUE INDEX fn_unique_idx ON table1 (UPPER(ITEM_NAME);
(note that unique constraints create unique indexes/constraints can be linked up with an index, hence me talking constraints and linking indexes. )
I'm sorry for capital letters & i will be use syntax highlighting.
Sorry i found miss understanding for why i use trigger to check unique item_name column instead of UNIQUE INDEX column.
T_ITEMS is table multiple users use it with different data @ same table, Sure more than one user insert items with same item_name with different USER# in table.
USER#1 add item_name "Dell PC VISTRO" in column item_name , I want create trigger before inserting or updating to check unique another item_name with same item_name in same USER#1.
For that i created function and trigger to do it.
But when update or insert in the table i found That ERROR TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512.
You do realize you have just defined a multi-column UNIQUE INDEX?
create unique index index_name_uq on table_name( user#, upper(item_name) );
Also, from a GUI Design stand point, (using APEX terms) you want to Validate that the data BEFORE you Submit.
(use a Page Level Validation)