Update row after insert from another table
I am using MySql and have two tables, master
and inventory
. Now I need a trigger after the inventory table field to insert an update product_description
from master
the table field product_description
.
Example:
main table:
pmid - product_name - product_description - price
1 tv HD tv 10
inventory table:
invid - pmid - product_description - color
1 1 black
The trigger should insert product_description from partmaster
where pmid = pmid.
CREATE TABLE IF NOT EXISTS `master` (
`pmid` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(255) NOT NULL,
`product_description` varchar(255) NOT NULL,
`price` varchar(10) NOT NULL,
PRIMARY KEY (`pmid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
CREATE TABLE IF NOT EXISTS `inventory` (
`invid` int(11) NOT NULL AUTO_INCREMENT,
`pmid` int(11) NOT NULL,
`product_description` varchar(255) NOT NULL,
`color` int(11) NOT NULL,
PRIMARY KEY (`invid`),
KEY `pmid` (`pmid`), //foreign key master table//
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
thank
source to share
delimiter //
CREATE TRIGGER update_inventory AFTER INSERT ON master
FOR EACH ROW
BEGIN
UPDATE inventory I SET I.product_description = new.product_description
WHERE (I.pmid = new.pmid );
END; //
delimiter ;
I find your data inconsistent: 1. How can you get the pmid in the inventory without having it in the master table? 2. The color field is numeric; contradicts your example.
source to share