i have 4 tables and i want to create a trigger that it declares a table and store it all in that table so it can be compared latter. In here are the tables that im using. But now i have a trigger that after insert on orders it inserts on transactions. My objective now its to create a trigger

Create a table in a trigger and compare it

i have 4 tables and i want to create a trigger that it declares a table and store it all in that table so it can be compared latter.

CREATE TABLE IF NOT EXISTS `inventory` (
  `id_product` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT,
  `model` varchar(45) NOT NULL,
  `price_new` FLOAT DEFAULT '0',
  `launch_date` DATE NOT NULL,
  `stock` INT DEFAULT '0',
  PRIMARY KEY (`id_product`));
 
CREATE TABLE IF NOT EXISTS `order` (
  `id_order` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT,
  `id_customer` INT(6) ZEROFILL NOT NULL,
  `subtotal` FLOAT DEFAULT '0',
  `discount` float DEFAULT '0',
  `tax_rate` float DEFAULT '0.23',
  `total` float DEFAULT '0',
  `date` DATETIME DEFAULT NOW(),
  PRIMARY KEY (`id_order`),
  CONSTRAINT `fk_order_1`
    FOREIGN KEY (`id_customer`)
    REFERENCES `iSAVE`.`customer` (`id_customer`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE);

  CREATE TABLE IF NOT EXISTS `order_item` (
  `id_order_item` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_product` INT(4) ZEROFILL NOT NULL,
  `id_order` INT(4) ZEROFILL NOT NULL,
  `quantity` INT DEFAULT NULL,
  FOREIGN KEY (`id_product`) REFERENCES `iSAVE`.`inventory` (`id_product`),
  FOREIGN KEY (`id_order`) REFERENCES `iSAVE`.`order` (`id_order`)
);

  CREATE TABLE IF NOT EXISTS `transaction` (
  `id_transaction` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_order` INT(4) ZEROFILL NOT NULL,
  `status` char(20) default 'Not completed',
  FOREIGN KEY (`id_order`) REFERENCES `iSAVE`.`order` (`id_order`)
);

In here are the tables that im using. But now i have a trigger that after insert on orders it inserts on transactions. My objective now its to create a trigger that generates a table that takes order and order item and store id_product id_order and quantity so it can be used to after update on transaction the stock is changed on the respective items on tabel inventory.

This is the trigger that i have so far but i cant use it because i dont rly know how to declare a table in mysql:

delimiter $$
CREATE TRIGGER trigg1 after update on `transaction` for each row
BEGIN
declare @item TABLE(
    id_order int,
    id_product int, 
    quantity int);
update inventory i join @item it using (id_product)
    set i.stock = i.stock - it.quantity
    where it.id_order = new.id_order;
end $$
delimiter ;

Answer

You can’t you have to use a temporaRY TABLE

delimiter $$
CREATE TRIGGER trigg1 after update on `transaction` for each row
BEGIN
CREATE TEMPORARY TABLE tbl1(
    id_order int,
    id_product int, 
    quantity int);
    
update inventory i join tbl1 it using (id_product)
    set i.stock = i.stock - it.quantity
    where it.id_order = new.id_order;
DROP TEMPORARY TABLE tbl1;
end $$
delimiter ;

But usually you make a temporaray atble with a select

CREATE TEMPORARY TABLE tbl1
SELECT id_order , 
       id_product , 
       quantity 
FROM ORDERS

Advertisement

Answer