0

I have to table named 'stuff' and 'given'. I wanted to add one more columnt to 'stuff' and after ALTER TABLE i used UPDATE. However this makes difference for only that moment. I mean, m new column will give the amount of stock after each given amount. 'stuff' has 'totalAmount' and given has 'amount'.

I used:

ALTER TABLE stuff ADD stock MEDIUMUNINT UNSIGNED;

UPDATE stuff SET stuff = totalAmount - (SELECT SUM(given.amount) FROM given WHERE id = given.productId);

This works for only that UPDATE. How can i make these two table so synchronized after each given amount, the stock will be affected either?

Thanks in advance :)

anyName
  • 113
  • 1
  • 2
  • 13
  • See this link.http://stackoverflow.com/questions/4236912/how-to-create-calculated-field-in-mysql. you cant add calculated field in table. instead you can create trigger which updates the value on inserting records – Annie Jul 29 '13 at 06:59
  • you should create triggers on `given` table, depending when you want change, `AFTER UPDATE/AFTER INSERT` or both to update `stuff` field in `stuff` table – mirkobrankovic Jul 29 '13 at 07:00

1 Answers1

0

Create an trigger on INSERT for the table "given" which will update the "stuff" in table "stuff"

CREATE TRIGGER [TRIGGER_ALTER_STUFF] ON [dbo].[given] 
FOR INSERT, UPDATE
AS
BEGIN
    UPDATE stuff SET stuff = totalAmount - (SELECT SUM(given.amount) FROM given WHERE id = given.productId);
END
Abhishek Jain
  • 2,597
  • 1
  • 18
  • 12