[SOLVED] help with a trigger

[Darkmatter5]

I have 2 tables

 

lvl2prod

levelproduction

02

15

 

plots

plot_idlevelprodvalue

102

215

 

Now I'm wanting to create a trigger for the prodvalue in plots.  I want it to get the production value from lvl2prod and update that value into prodvalue when level in plots is updated.

 

Can you have it pull data from another table to use as what to update a field to?

[Darkmatter5]

I've got this so far, but it's not working.

 

CREATE TRIGGER calc_prodvalue
AFTER UPDATE ON plots
FOR EACH ROW
BEGIN
DECLARE pv INTEGER;

SELECT l.production
INTO pv
FROM lvls2prod l, plots p
WHERE p.level=l.level;

SET p.prodvalue=pv;
END;

 

I get this error, "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 ".

[fenway]

Where did you reset the delimeter?

[Darkmatter5]

I uhh, I guess I didn't I'm just trying to figure this out from a tutorial online.  I copied their code and modified it to suit my needs.

[Darkmatter5]

Okay I've come up with this

 

DELIMITER |

CREATE TRIGGER calc_prodvalue
AFTER UPDATE ON plots
FOR EACH ROW
BEGIN
DECLARE pv INTEGER;

SELECT l.production
INTO pv
FROM lvls2prod l, plots p
WHERE p.level=l.level;

UPDATE plots
SET NEW.prodvalue=pv;
END;
|

DELIMITER ;

 

Now I get this error: "#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' "

[HuggieBear]

This means that you already have an 'AFTER UPDATE' trigger on the 'plots' table.

 

You can only have one trigger of each event type.

 

Regards

Rich

[Darkmatter5]

Okay I deleted the old trigger and re-ran the code and got this error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1

[HuggieBear]

Are you running this from the command line client?  Not through phpMyAdmin or something like that?

 

Rich

[Darkmatter5]

I ran it through phpmyadmin.  I found a post online that has some interesting info:

 

http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/

 

Here's the new code:

DELIMITER |

CREATE TRIGGER calc_prodvalue
BEFORE UPDATE ON plots
FOR EACH ROW

BEGIN
DECLARE pv INTEGER;

SELECT l.production
INTO pv
FROM lvls2prod l, plots p
WHERE p.level=l.level
AND p.plot_id=NEW.plot_id;

SET NEW.prodvalue=pv;
END;
|

DELIMITER ;

 

The problem is having to do this it doesn't update the value to the NEW value, but the OLD value before the value changes.  So the prodvalue is always old, not the new/current value based on level.

[HuggieBear]

It's best not to do this through phpMyAdmin as it doesn't support it properly.  Do it through the command line.

 

Regards

Rich

[Darkmatter5]

Got it working, this did it.

 

DELIMITER |

CREATE TRIGGER calc_prodvalue
BEFORE UPDATE ON plots
FOR EACH ROW

BEGIN
SET NEW.prodvalue=(SELECT production FROM lvls2prod WHERE level=NEW.level);
END;
|

DELIMITER ;