Trigger Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger Error

Author

Topic

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-09-20 : 05:47:27

I have 2 tables, Portfolio and Transactions. When you insert Transaction_Amount value into the Transactions, the Portfolio should be updated too.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?

alinazolii
Starting Member

4 Posts

Posted - 2014-09-20 : 07:40:40

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.

quote:
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources