Example of triggers updates

trigger-update.sql

DROP TABLE IF EXISTS agro_produce_prices_histories;
CREATE TABLE agro_produce_prices_histories (
	agro_produce_prices_history_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Price History ID',
	price_date DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'Date of produce price change',
	produce_id INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Produce ID',
	produce_price FLOAT(6,2) UNSIGNED NOT NULL DEFAULT '0.00' COMMENT 'Last Price',
	PRIMARY KEY (agro_produce_prices_history_id)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS agro_produces;
CREATE TABLE agro_produces (
	produce_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Produce ID',
	produce_name VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Produce Name',
	produce_code VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Produce Code',
	produce_price FLOAT(6,2) NOT NULL DEFAULT '0.00' COMMENT 'Current Produce Price',
	PRIMARY KEY (produce_id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE TRIGGER t_prices_histories_after_update AFTER UPDATE ON agro_produces
FOR EACH ROW
BEGIN
	INSERT INTO agro_produce_prices_histories (
		price_date, produce_id, produce_price
	) VALUES (
		CURRENT_DATE(), new.produce_id, new.produce_price
	);
END $$

INSERT INTO agro_produces(
	produce_id, produce_name, produce_code, produce_price
) VALUES (
	'Garlic', 'G0023', '4.95'
);

UPDATE agro_produces SET produce_price='5.10' WHERE produce_id='1';
UPDATE agro_produces SET produce_price='6.35' WHERE produce_id='1';

SELECT * FROM agro_produce_prices_histories;