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
– BugFinder, Commented Jun 16, 2011 at 20:05
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
– Ryan, Commented Jun 16, 2011 at 20:06
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
– BugFinder, Commented Jun 16, 2011 at 20:08
That makes a lot of sense now. I did not realize that. Wow I was deceivied — Ryan
– Ryan, Commented Jun 16, 2011 at 20:11

MGZero · Accepted Answer · 2011-06-16 20:05:42Z

2

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

answered Jun 16, 2011 at 20:05

MGZero

6,0225 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan Over a year ago

This worked. Now, It did INSERT the OLD.salePrice, but it did not Insert the modelNumber

MGZero Over a year ago

What happens with modelNumber now?

Ryan Over a year ago

I just had to change it OLD.modelNumber

BugFinder · Accepted Answer · 2011-06-16 20:12:46Z

1

Try then changing it to

insert into history_price (old.ModelNumber,old.salePrice)

answered Jun 16, 2011 at 20:12

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL Trigger-- Not inserting correct values

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related