Trigger After Update - ERROR 1442 (HY000)

[s0c0]

I'm trying to create a trigger that updates a field each time another field in the same table is changed.  I created a numerical hash of a varchar column for increasing speed on lookups and group by operations.  However, I get the following error:

 

MySQL Trigger: ERROR 1442 (HY000): Cant update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

 

It looks like MySQL is preventing the trigger from executing because it would create an infinite loop.  Here is my trigger syntax:

 

delimiter $$
CREATE TRIGGER pHash

AFTER UPDATE ON products FOR EACH ROW
BEGIN
UPDATE products SET model_hash = crc32(products_model);
END$$
delimiter ;

 

Any idea on how I could accomplish this?  Doing this in the code is not an option because we have a very old code base where updates occur all over the place, not very OOP at all so this must occur within the database engine.

[mikosiko]

Trigger/Function limitation... (I think that I did answer something exactly like this yesterday)... in a trigger/function you can not execute any other SQL sentence that involve the same table used for the trigger/function.... (INSERT,UPDATE, DELETE,  TRUNCATE, DROP, etc..etc)

 

but in your triggers (BEFORE UPDATE, BEFORE INSERT) you can assign/calculate your field model_hash value as you want, no need to use another UPDATE clause... per example in a BEFORE UPDATE you can access the OLD and the NEW values of any field

 

look for examples here: http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

[s0c0]

Can you give me a hint on the syntax here?  I tried this:

 

CREATE TRIGGER pHash
BEFORE UPDATE ON products FOR EACH ROW
BEGIN
SET NEW.model_hash = crc32(NEW.products_model)
END; 

 

Did not work.

 

[s0c0]

Nevermind the last reply.  I have got it working:

 

CREATE TRIGGER pHash
BEFORE UPDATE ON products FOR EACH ROW SET NEW.model_hash = crc32(NEW.products_model);

 

[mikosiko]

Nevermind the last reply.  I have got it working, but the only problem is its doing the calculation for each row on an update, even if only one row is updated. .....

 

That is impossible if your process is updating just one row... the trigger is going to act only over the affected row(s) in whatever update that you are executing... check your update not the trigger.

 

Here is the current syntax:

CREATE TRIGGER pHash
BEFORE UPDATE ON products FOR EACH ROW SET NEW.model_hash = crc32(NEW.products_model);

 

the syntax that you showed in the second time is incorrect..  first one was close ...just modify it slightly  and pay attention to the DELIMITER when you create it (maybe that was the error in your first intent):

 

CREATE TRIGGER pHash
BEFORE UPDATE ON products
FOR EACH ROW 
BEGIN
SET NEW.model_hash = crc32(NEW.products_model);
END