Create trigger in Mysql error while adding new row

Question

I have two tables model_detail with columns

`id``model_id``productName``Color``Available_Quantity`

and model_stock with columns

`id``model_detail_id``entry_date``amount`

model_detail_id is the foreign key pointing to model_detail(id) table I want to update the Available_Quantity of model_detail table whenever new model_stock is added and I have tried something like this

CREATE TRIGGER Update_model_stock AFTER INSERT ON model_stock
FOR EACH ROW 
BEGIN
UPDATE model_detail
SET Available_Quantity= Available_Quantity + NEW.amount
WHERE model_detail.id = NEW.model_stock.model_detail_id;
END

but when I add a row to the model_stock table I get the error something like this

SQL query:
INSERT INTO `model_stock` (`id`, `model_detail_id`, `entry_date`, `amount`) 
VALUES (NULL, '2', '2017-07-11', '20')

The error message I received:

MySQL said: Documentation #1054 - Unknown column 'NEW.model_stock.model_detail_id' in 'where clause'

Shadow · Accepted Answer · 2017-07-19 03:02:11Z

1

You must not add the table name after the NEW keword:

...
WHERE model_detail.id = NEW.model_detail_id;
...

answered Jul 19, 2017 at 3:02

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create trigger in Mysql error while adding new row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related