CODEWITHSUNDEEP
mysqltriggers
BernardA
BernardA

Reputation: 1523

mysql trigger on update to populate another table

I am new to this TRIGGER thing and it is beating me up pretty good.

The intention is to populate a price history table every time a price change occurs on table prices.

So far my attempts are taking all prices changes each time one price is updated, or not working at all.

I basically did not get this logic, so that I can limit it to the one price being updated, not all of the updates.

This below is a bit of a pseudo-code.

DELIMITER //

CREATE TRIGGER price_update
AFTER UPDATE
ON prices FOR EACH ROW

BEGIN

 INSERT INTO prices_history (version_id,price,data_change)
 SELECT version_id, price, time_update FROM prices WHERE????? ;
 END //
 DELIMITER ;

table prices (summarized)

version_id smallint(4) primary
price decimal (10,0)
time_update timestamp

table prices_history

price_id int(5) primary
version_id smallint(4)
price decimal (10,0)
data_change datetime

Upvotes: 1

Views: 874

Answers (2)

Marki555
Marki555

Reputation: 6860

Don't do SELECT inside the trigger, as that will use all the rows from table. You have available the columns from the changed row directly. Also you can use OLD. and NEW. for distinguishing between the values before and after UPDATE.

DELIMITER $$
CREATE TRIGGER price_update
    BEFORE UPDATE ON prices
    FOR EACH ROW
    BEGIN

    INSERT INTO prices_history SET
    price = NEW.price,
    version_id = NEW.version_id, // or OLD.version_id
    data_change = NEW.time_update; 
END$$
DELIMITER ;

Upvotes: 1

Nagasimha Iyengar
Nagasimha Iyengar

Reputation: 461

prices table should have price_id - not version_id

Then 

select @max_version = max(version_no)+1 from price_history where price_id = @changed_price_id
INSERT INTO prices_history (price_id, version_id,price,data_change)
 SELECT price_id_id, @max_version, price, time_update FROM prices WHERE price_id = @changed_price_id

;

Upvotes: 0

Related Questions