Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL Tables trigger
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 Smile | :)
 
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
);
Posted 13-Sep-12 2:22am
Edited 13-Sep-12 2:45am
Malli_S25.6K
v2
Comments
Zoltán Zörgő at 13-Sep-12 7:34am
   
Let's clarify:
You want a trigger on koszyk table, that on insert calculates the value of koszyk_zarobek field of the newly inserted row (NEW.koszyk_zarobek) by multiplying the same row's NEW.koszyk_ilosc field with the value of the produkty.produkt_zarobek where produkty.product_id=new.produkt_id? Do I see it correctly?
(By the way, you have a cross reference between these tables)
Member 9152265 at 13-Sep-12 7:40am
   
Yes, u see it corretly, maybe i should write it more clerarly:
1. Takes the value from table produkty: produkt_zarobek
2. Takes the value from table koszyk: koszyk_ilosc
3. Multiplies one by another
4. Fills in the row in table koszyk: koszyk_zarobek
 
koszyk(koszyk_zarobek) = koszyk(koszyk_ilosc) * produkty(produkt_zarobek)
 
Seems so straight, but i'm stuck :/
 
I you don't mind asking, where is the cross refernece?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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` )
and the inverse direction:
CONSTRAINT `produkt_id` FOREIGN KEY (`produkt_id` )  REFERENCES `rowerowy`.`produkty` (`produkt_id`)</pre>
  Permalink  
Comments
Member 9152265 at 13-Sep-12 10:03am
   
Got it now, thank you very much :) You've made it clear for me now :)
Zoltán Zörgő at 13-Sep-12 10:49am
   
You'r welcome.
Member 9152265 at 15-Sep-12 19:29pm
   
i've done finally some deeper checks with WAMP Server and i've made the whole of my MySQL script work, unfotunately, MySQL does't support such triggers and it says that some day mysql will accept them :/ i've tried doing some other triggers but none of them work :/ is it even possible to make a trigger working with two different tables, because i'm in a serious doubt now and i can't really make the database work without this one simple thing :/ i know it works on ORACLE database to use variables from 2 diff. tables, if u don't mind, would you have some other ideas :) ?
Zoltán Zörgő at 16-Sep-12 2:57am
   
Well, before you continuously accept and redo first check what your version of MySQL is capable of - or how you want to us it is ok or not. There is a wide API documentation on MySQL site. There is a note you should consider: Important
MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server;
.
If you google a little bit, you will find that my answer is correct, and will work if the conditions are met. Since you did not specify any constraints, and I can not see your screen, read your mind or your hard drive, I can not think or google instead of you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 13 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100