how to update a table based on previous entries in mysql

Question

I am having a serious trouble using triggers in mysql. Basically I am trying to update a row on after insert. I am using the existing value in the row and trying to update an other column of same row.

Ex:

Existing_Name Actual_Name
Product_Arc1  Arc1
Product_Arc2  Arc2
Product_Arc3

I am taking the value of Existing_Name and extracting the second part of it and updating the Actual_Name using triggers. BUt since MySQL does not support updating the same table the trigger was activated on? is there any possible way to do what I want?

As of now, I am using PHP script to perform the same effect. But this requires the end user to keep triggering this script from the web browser whenever there are some changes in the database.

Any ideas appreciated.

Thanks.

Answer 1

Can't you directly change the insert mechanism? insert into table(Existing_Name,Actual_Name) values ($Existing_Name, replace($Existing_Name,'Product_','')...

Answer 2

Does it have to be an "one after insert" trigger? This would be the case if you needed an auto_increment value to calculate the value of the field you want to modify. Otherwise a "before insert" trigger should do. You can change values of fields to be inserted via SET NEW.fieldname = newvalue

E.g. let's take the table

CREATE TABLE foo (id int auto_increment, x int, y int, primary key(id))

and create a before insert trigger

delimiter |

CREATE TRIGGER footrigger BEFORE INSERT ON foo
  FOR EACH ROW BEGIN
    IF NEW.y > NEW.x THEN
       SET NEW.y = -1;
    END IF;
  END;
|

And now let's insert two records, one where y=x and one where y>x

INSERT INTO foo (x,y) VALUES (1,1), (1,2)

A SELECT x,y FROM foo then returns

"x";"y"
"1";"1"
"1";"-1"