Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What i'm doing wrong?

CREATE TRIGGER `Calc` AFTER INSERT on `test`.`bookings` FOR EACH ROW 
BEGIN
UPDATE `test`.`article` AS `ST`
SET `ST`.`stock` = SUM(`test`.`bookings`.`amount`)
WHERE `ST`.`articlenr` = `test`.`bookings`.`NEW.article`;
END

The idea is, to calculate "STOCK" in ARTICLE, after an insert like:

Amount: 1 Article: 123 

on table bookings.

How can this be solved?

share|improve this question

closed as off-topic by Paul White, bluefeet, RolandoMySQLDBA, Jack Douglas Dec 17 '13 at 13:51

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, bluefeet, RolandoMySQLDBA, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Try: SET ST.stock = ST.stock + NEW.amount WHERE ST.articlenr = NEW.article; –  ypercube Dec 16 '13 at 20:35
    
No, that would be wrong. Because it should calculate the sum of all bookings, what happens if i delete one?, then is the stock wrong –  user3101966 Dec 16 '13 at 20:38
1  
@user3101966 actually it would be wrong your way, too, until the next insert; if you're going to keep denormalized data, you have to maintain it wherever you manipulate the source... so you need update and delete triggers, too, not just insert. –  Michael - sqlbot Dec 16 '13 at 20:41
    
You'll need a DELETE trigger, too, anyway. What do you think your code will do (even if corrected to calculate over all bookings) after a booking is deleted (and until a new one is inserted - which may happen days later)? –  ypercube Dec 16 '13 at 20:41
    
No, the delete-booking is like Set amount to 0, and set a flag named "deleted", to see that this was deleted. –  user3101966 Dec 16 '13 at 20:53

1 Answer 1

up vote 1 down vote accepted

You need a trigger for each operation on your table: Insert, Update and Delete

To demonstrate, I created a SQL Fiddle

First let's create the tables and the triggers:

MySQL 5.5.32 Schema Setup:

CREATE TABLE bookings(id INT, articlenr INT, amount INT);
CREATE TABLE article(articlenr INT, stock INT);

//

CREATE TRIGGER Calc_INS AFTER INSERT on bookings FOR EACH ROW 
BEGIN
  UPDATE article AS A
     SET A.stock = A.stock + NEW.amount
   WHERE A.articlenr = NEW.articlenr;
END;

//

CREATE TRIGGER Calc_UPD AFTER UPDATE on bookings FOR EACH ROW 
BEGIN
  UPDATE article AS A
     SET A.stock = A.stock - OLD.amount
   WHERE A.articlenr = OLD.articlenr;

  UPDATE article AS A
     SET A.stock = A.stock + NEW.amount
   WHERE A.articlenr = NEW.articlenr;
END;

//

CREATE TRIGGER Calc_DEL AFTER DELETE on bookings FOR EACH ROW 
BEGIN
  UPDATE article AS A
     SET A.stock = A.stock - OLD.amount
   WHERE A.articlenr = OLD.articlenr;
END;

Because of a SQLFiddle limitation we also need procedures to insert, update and delete bookings. Note that these are not required to make the triggers work, only to make SQLFiddle work...

CREATE PROCEDURE add_booking(
  pId INT,
  pArticlenr INT,
  pAmount INT
)
BEGIN
  INSERT INTO bookings VALUES(pId, pArticlenr, pAmount);
END;

//

CREATE PROCEDURE change_booking(
  pId INT,
  pArticlenr INT,
  pAmount INT
)
BEGIN
  UPDATE bookings 
     SET articlenr = pArticlenr,
         amount = pAmount
   WHERE id = pId;
END;

//

CREATE PROCEDURE delete_booking(
  pId INT
)
BEGIN
  DELETE FROM bookings 
   WHERE id = pId;
END;

Finally we need some articles:

INSERT INTO article VALUES(1,0);
INSERT INTO article VALUES(2,0);

Now let's look at our current inventory:

Query 1:

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |     0 |
|         2 |     0 |

There is currently no stock available. To change that lets create a booking and check the inventory again:

Query 2:

CALL add_booking(100, 1, 21);

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |    21 |
|         2 |     0 |

As you can see, the stock was updated correctly. Because the add_booking procedure executes an INSERT statement, the calc_INS trigger fired and updated the inventory.

To see that this works with multiple bookings for the same article let's create three additional ones of them:

Query 3:

CALL add_booking(200, 2,  42);
CALL add_booking(201, 2, 101);
CALL add_booking(202, 2, 210);

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |    21 |
|         2 |   353 |

Again, all actions were INSERTs, so only the calc_INS trigger fired.

Now let's change the amount on one of the bookings:

Query 4:

CALL change_booking(201, 2, 1);

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |    21 |
|         2 |   253 |

change_booking executes an UPDATE statement. This causes the calc_UPD trigger to fire. In that trigger the first UPDATE statement removes the old amount from the inventory. The second UPDATE then adds the new amount to the inventory.

I implemented this as two separate update statements to allow for the article to be changed on a booking:

Query 5:

CALL change_booking(201, 1, 1);

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |    22 |
|         2 |   252 |

The first UPDATE in the calc_UPD trigger removed (subtracted) the amount from the article 2 and the second UPDATE then added the new amount to article 1.

You said you are using only soft deletes. Soft deletes can be handled by the update trigger. For completeness sake I included a delete trigger in this example anyway.

Query 6:

CALL delete_booking(100);

SELECT * FROM article;

Results:

| ARTICLENR | STOCK |
|-----------|-------|
|         1 |     1 |
|         2 |   252 |

delete_booking executes a DELETE statement. That causes the calc_DEL trigger to fire. It subtracts the old amount from the article's inventory.

Instead of trying to keep the stock in sync with the bookings you could also create a view that joins the articles table (without the stock column) with the bookings table to calculate the stock on the fly when needed. This sounds like a lot of overhead, but it is not actually that bad if you use the right indexing strategy and it is a lot easier to maintain in the long run than triggers.

Such a view could look like this:

CREATE VIEW stock
AS
SELECT A.articlenr , COALESCE(SUM(B.amount),0) AS stock
  FROM article AS A
  LEFT JOIN bookings AS B
    ON A.articlenr = B.articlenr
 GROUP BY A.articlenr;

Here is another SQLFiddle that demonstrates the view without triggers.

share|improve this answer
    
Hi. Thank you for your the detailed explanation of this. I used this as the working solution. I modificated the Update Line to a SUM-function instead of a + function, in my Trigger. Thank you! –  user3101966 Dec 17 '13 at 12:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.