MYSql链接来自2个表的数据触发器-使用一个计算值并放入另一个 [英] MYSql linking data from 2 tables trigger - calculating value using one and putting into another

问题描述

我在触发器中遇到一个问题，该触发器将计算从一个表行中获取值并将其与来自另一表中的另一行相乘并放入值中的数据.我想到了这样一个触发器:

I have a problem in a trigger that will calculate the data taking values from one table row and multiplying it by another from different table and putting the value in. I come up with such a trigger:

DELIMITER $$

CREATE TRIGGER liczy_zarobek_koszyk
    BEFORE INSERT
    ON koszyk
FOR EACH ROW BEGIN
INSERT FROM koszyk SET a = NEW.koszyk_ilosc;    - ERROR
INSERT FROM produkty SET b = NEW.produkt_zarobek;   -ERROR
UPDATE koszyk SET koszyk_zarobek = a * b;
END;
$$

但似乎有错误.对于解决问题的任何建议，我将不胜感激:)

表格:

but it seems to have errors. I would appreciate any advise in fixing the problem :)

TABLES:

CREATE TABLE `rowerowy`.`koszyk` (

  `koszyk_id` INT NOT NULL AUTO_INCREMENT ,

  `koszyk_ilosc` INT(10) NULL ,

  `koszyk_data` DATETIME NOT NULL DEFAULT NOW() , -- przy kazdym dopisaniu dopisuje date dopisania z teraz:)

  `koszyk_zarobek` DECIMAL NULL,

  `produkt_id` INT NOT ,

  PRIMARY KEY (`koszyk_id`),

  CONSTRAINT `produkt_id`

    FOREIGN KEY (`produkt_id` )

    REFERENCES `rowerowy`.`produkty` (`produkt_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION
);

CREATE  TABLE `rowerowy`.`produkty` (

  `produkt_id` INT NOT NULL AUTO_INCREMENT ,

  `produkt_producent` VARCHAR(45) NULL ,

  `produkt_model` VARCHAR(45) NULL ,

  `produkt_rok` INT(4) NULL ,

  `produkt_cena_kupna` DECIMAL NULL ,

  `produkt_cena` DECIMAL NULL ,

  `produkt_zarobek` DECIMAL NULL ,

  `produkt_ilosc` INT(10) NULL ,

  `kategoria_id` INT NOT NULL ,

  `dostawcy_id` INT NOT NULL ,

  `koszyk_id` INT NOT NULL ,

  PRIMARY KEY (`produkt_id`) ,

  INDEX `koszyk_id` (`koszyk_id` ASC) ,

  INDEX `dostawcy_id` (`dostawcy_id` ASC) ,

  INDEX `kategoria_id` (`kategoria_id` ASC) ,

  CONSTRAINT `koszyk_id`

    FOREIGN KEY (`koszyk_id` )

    REFERENCES `rowerowy`.`koszyk` (`koszyk_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `dostawcy_id`

    FOREIGN KEY (`dostawcy_id` )

    REFERENCES `rowerowy`.`dostawcy` (`dostawcy_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `kategoria_id`

    FOREIGN KEY (`kategoria_id` )

    REFERENCES `rowerowy`.`kategoria` (`kategoria_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION
);

推荐答案

创建 TRIGGER liczy_zarobek_koszyk 插入之前 打开 koszyk FOR 每行 BEGIN 插入 FROM koszyk SET a =新. koszyk_ilosc; - 错误 插入 FROM 产品 SET b =新. produkt_zarobek; -错误 更新 koszyk SET koszyk_zarobek = a * b; END ;

CREATE TRIGGER liczy_zarobek_koszyk BEFORE INSERT ON koszyk FOR EACH ROW BEGIN INSERT FROM koszyk SET a = NEW.koszyk_ilosc; - ERROR INSERT FROM produkty SET b = NEW.produkt_zarobek; -ERROR UPDATE koszyk SET koszyk_zarobek = a * b; END;

但似乎有错误.对于解决问题的任何建议，我将不胜感激:)

表格:

but it seems to have errors. I would appreciate any advise in fixing the problem :)

TABLES:

CREATE TABLE `rowerowy`.`koszyk` (

  `koszyk_id` INT NOT NULL AUTO_INCREMENT ,

  `koszyk_ilosc` INT(10) NULL ,

  `koszyk_data` DATETIME NOT NULL DEFAULT NOW() , -- przy kazdym dopisaniu dopisuje date dopisania z teraz:)

  `koszyk_zarobek` DECIMAL NULL,

  `produkt_id` INT NOT ,

  PRIMARY KEY (`koszyk_id`),

  CONSTRAINT `produkt_id`

    FOREIGN KEY (`produkt_id` )

    REFERENCES `rowerowy`.`produkty` (`produkt_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION
);

CREATE  TABLE `rowerowy`.`produkty` (

  `produkt_id` INT NOT NULL AUTO_INCREMENT ,

  `produkt_producent` VARCHAR(45) NULL ,

  `produkt_model` VARCHAR(45) NULL ,

  `produkt_rok` INT(4) NULL ,

  `produkt_cena_kupna` DECIMAL NULL ,

  `produkt_cena` DECIMAL NULL ,

  `produkt_zarobek` DECIMAL NULL ,

  `produkt_ilosc` INT(10) NULL ,

  `kategoria_id` INT NOT NULL ,

  `dostawcy_id` INT NOT NULL ,

  `koszyk_id` INT NOT NULL ,

  PRIMARY KEY (`produkt_id`) ,

  INDEX `koszyk_id` (`koszyk_id` ASC) ,

  INDEX `dostawcy_id` (`dostawcy_id` ASC) ,

  INDEX `kategoria_id` (`kategoria_id` ASC) ,

  CONSTRAINT `koszyk_id`

    FOREIGN KEY (`koszyk_id` )

    REFERENCES `rowerowy`.`koszyk` (`koszyk_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `dostawcy_id`

    FOREIGN KEY (`dostawcy_id` )

    REFERENCES `rowerowy`.`dostawcy` (`dostawcy_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `kategoria_id`

    FOREIGN KEY (`kategoria_id` )

    REFERENCES `rowerowy`.`kategoria` (`kategoria_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION
);

尝试以下操作:

Try this one:

CREATE TRIGGER liczy_zarobek_koszyk
    BEFORE INSERT
    ON koszyk
FOR EACH ROW BEGIN
    SET NEW.koszyk_zarobek = NEW.koszyk_ilosc * (SELECT produkt_zarobek FROM produkty WHERE product_id=NEW.produkt_id);
END;

这是交叉引用:

Here is the cross reference:

CONSTRAINT `koszyk_id` FOREIGN KEY (`koszyk_id` ) REFERENCES `rowerowy`.`koszyk` (`koszyk_id` )

以及相反的方向:

CONSTRAINT `produkt_id` FOREIGN KEY (`produkt_id` )  REFERENCES `rowerowy`.`produkty` (`produkt_id`)</pre>

这篇关于MYSql链接来自2个表的数据触发器-使用一个计算值并放入另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！