Forum Stats

  • 3,872,750 Users
  • 2,266,466 Discussions
  • 7,911,291 Comments

Discussions

Problem with a trigger

user12282526
user12282526 Member Posts: 5
edited Apr 19, 2011 1:24PM in SQL & PL/SQL
Hi all,

I want to create a trigger on below products table(it's just an example, my real table has another title and another columns)

ID NAME PRICE
1 Name1 10
2 Name2 20
3 Name1 30
4 Name1 40
5 Name3 50

And when i will update Name1 for ID=1 to Name999, i want that all records with name='Name1' to be changed to 'Name999'.

I created a trigger for this:

CREATE OR REPLACE TRIGGER trg_test
before insert or update ON PRODUCTS for each row
begin
update PRODUCTS a
set a.name=:new.name
where a.name<> :new.name;
end;


And than i checked to see if it's working:

update products
set name='NAME999'
where id=1;

Here is the error

ORA-04091: table PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at "trg_test", line 4
ORA-04088: error during execution of trigger 'trg_test'

There is another way to create the trigger? Or if is not, could be another way to update those columns?

Thanks,
Madalin

Edited by: user12282526 on Apr 19, 2011 10:11 AM
Tagged:

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,867 Silver Crown
    edited Apr 19, 2011 1:11PM
    Sounds to me like a design problem, not a trigger problem.

    If you had a NAMES table (preferably more descriptive) you could point to it with an ID in your products table.

    Then you would only need to change Name1 to Name999 in one place and only 1 row. You wouldn't even need to change the products table at all. (or need a trigger)
  • Thanks, but i don't have another table with the NAMES and i have to use only this table.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Are you saying that refactoring the table definition so that your database is properly normalized is out of the question? Because that is by far the easiest option.

    Can you guarantee that there will never be an update to the table that would create a conflict? What would you want to happen, for example, if someone ran the update
    UPDATE table_name
       SET name = 
            (case when id = 1 then 'Name999' 
                  when id = 3 then 'Name789' 
                  else name 
             end)
     WHERE name = 'Name1'
    That would update one row with a name of 'Name1' to 'Name999', another to 'Name789', and the others to 'Name1'. Which of those three would be the correct value for 'Name1'?

    Assuming that you can guarantee that there will be no conflicts, it is technically possible to do what you want though it's rather cumbersome. You would need
    - A package with a collection that maps old names to new names (or a temporary table to use for mapping)
    - A before statement trigger that initializes this collection
    - A row-level trigger that populates the collection (or the temporary table)
    - An after statement trigger that processes the data in the collection and issues the update(s).

    Justin
This discussion has been closed.