MySQL Trigger-- Not inserting correct values

Question

The goal of this trigger is to insert old data per row into a table if the updated data per row does not equal the current data per row.

delimiter //
CREATE TRIGGER history BEFORE UPDATE ON table1

FOR EACH ROW
BEGIN
IF NEW.salePrice <> OLD.salePrice THEN

INSERT INTO history_price (modelNumber,salePrice)
        VALUES ('modelNumber','OLD.salePrice');

        ELSEIF NEW.salePrice = OLD.salePrice THEN
            SET NEW.salePrice = OLD.salePrice;

    END IF;

 END;//
 delimiter ;

All in all, this concept should be working, but the insert is not working. It is only inserting the actual text, not the values.

More specifically:

    INSERT INTO history_price (modelNumber,salePrice)
    VALUES 
        ('modelNumber','OLD.salePrice');

This part of my trigger literally inserts modelNumber, and OLD.salePrice into my history table if the updated values are not equal.

What is wrong with this query?

Dont you want to just remove the quotes round the 2 fields? Or am I being stupidly thick? — BugFinder
I want to insert the values. Im doing an update on those specific fields and I want to insert the old fields' row content. — Ryan
I know, but by putting single quotes round the field names, you would be just as good as writing insert into history_price (modelNumber,salePrice) values ("joe","blogs") — BugFinder
That makes a lot of sense now. I did not realize that. Wow I was deceivied — Ryan

MGZero MGZero · Accepted Answer · 2011-06-16T20:05:42

2

votes

modelNumber and OLD.salePrice probably should not be in single quotes.

MySQL Trigger-- Not inserting correct values

2 Answers