MySQL Forums
Forum List  »  InnoDB

Problem creating trigger
Posted by: Don Ireland
Date: December 19, 2015 09:14AM

Hi All. I'm trying to create a trigger that runs before insert.

Here's my trigger as written.

DROP TRIGGER IF EXISTS onTransactionInsert;
DELIMITER |
CREATE TRIGGER onTransactionInsert BEFORE INSERT ON `transactions`
FOR EACH ROW
BEGIN
SET new.runningBalNormal = new.amount + SELECT sum(`amount`) FROM `transactions`
WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) <= `date` AND
`userID` = new.userID AND `acctID` = new.acctID;

END |
DELIMITER ;

Now what has me really confused is that the following line works fine just entered as SQL. So why is it throwing an error when I try to use it in the trigger?

SELECT sum( `amount` )
FROM `transactions`
WHERE DATE_SUB( CURDATE( ) , INTERVAL 30
DAY ) <= `date`
AND `userID` =4
AND `acctID` =1

TIA!

Don

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem creating trigger
1457
December 19, 2015 09:14AM
920
December 19, 2015 09:17AM
669
December 19, 2015 10:27AM
897
December 19, 2015 11:07AM
737
December 19, 2015 11:21AM
658
December 19, 2015 11:33AM
680
December 19, 2015 03:07PM
633
December 19, 2015 06:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.