CODEWITHSUNDEEP
mysqlsqldatabasetriggers
PeakGen
PeakGen

Reputation: 22995

Error in SQL Trigger

I have 2 tables, Portfolio and Transactions. When you insert Transaction_Amount value into the Transactions, the Portfolio should be updated too. Below is the table structure.

enter image description here

I created a Trigger so whenever I insert a value into the Transactions, the Portfolio will be updated too.

Below is my MYSQL Trigger

USE `custom_sample`;
DELIMITER $$
CREATE TRIGGER `Transactions_AINS` AFTER INSERT ON `Transactions` FOR EACH ROW
UPDATE Portfolio 
SET Invest_Amount = Invest_Amount+Transactions.Transaction_Amount
where
Portfolio.idPortfolio = Transactions.idPortfolio

However this didn't work. It says Unknown column Transactions.idPortfolio in where clause

What is wrong with my script?

Upvotes: 0

Views: 32

Answers (1)

juergen d
juergen d

Reputation: 204746

The record of the triggered table can be referenced by NEW (or OLD for the values before in case of an update) instead of the table name.

CREATE TRIGGER `Transactions_AINS` AFTER INSERT ON `Transactions` 
FOR EACH ROW
    UPDATE Portfolio 
    SET Invest_Amount = Invest_Amount + NEW.Transaction_Amount
    WHERE idPortfolio = NEW.idPortfolio

Upvotes: 1

Related Questions