CODEWITHSUNDEEP
mysqltriggers
Ryan
Ryan

Reputation: 14649

MySQL Trigger: Can I use Update?

CREATE TRIGGER history BEFORE UPDATE ON bestbuy

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

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

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

    END IF;

 END

My question: is it valid to use

CREATE TRIGGER history BEFORE REPLACE ON bestbuy

Upvotes: 1

Views: 136

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

No, it is not - valid events are only INSERT, UPDATE and DELETE.

Are you sure you are updating data with REPLACE? - you could change that, to update it with INSERT ... ON DUPLICATE KEY UPDATE - so your trigger will run

Edit to address the comments

take a look at INSERT ... ON DUPLICATE KEY UPDATE Syntax on the MySQL site - it's an ordinary INSERT, with a clause in it - I also think its a better performer than REPLACE, because this does not delete the record to insert it again, it just updates the values

Upvotes: 1

Related Questions