CODEWITHSUNDEEP
mysql
Nameless
Nameless

Reputation: 523

Create trigger in Mysql error while adding new row

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'

Upvotes: 0

Views: 33

Answers (1)

Shadow
Shadow

Reputation: 34304

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

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

Upvotes: 1

Related Questions