MYSql linking data from 2 tables trigger - calculating value using one and putting into another

Question

4.00/5 (1 vote)

See more:

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:

SQL

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:

SQL

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
);

SQL

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 1:22am

Member 9152265

Updated 13-Sep-12 1:45am

Malli_S

v2

Add a Solution

Comments

Zoltán Zörgő 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Zoltán Zörgő** · Answer 1 · 2012-09-13T02:49:00

Solution 1

Try this one:

SQL

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:

SQL

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

and the inverse direction:

SQL

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

Posted 13-Sep-12 2:49am

Zoltán Zörgő

Comments

Member 9152265 13-Sep-12 10:03am

Got it now, thank you very much :) You've made it clear for me now :)

Zoltán Zörgő 13-Sep-12 10:49am

You'r welcome.

Member 9152265 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ő 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.