MySQL Forums
Forum List  »  Triggers

Creating trigger succeeds, but variables not working
Posted by: Benjamin McWhorter
Date: March 30, 2012 09:41AM

Hi, I'm having some huge difficulties with some variables in a simple trigger I was trying to write. I've tried this both ways -- declaring the variables and the way I am about to post and both show they have the correct syntax, but when you go to execute, they get errors. this code works well up until the while loop. In fact the SELECT DATE_FORMAT(@Out_Next_Expected_Due_Date,'%w'); statement will correctly print what I expect. Please help! I'm using mysql version 5.1.61. I will be monitoring this closely so any questions will be promptly answered. Thanks in advance.

-Ben

SELECT @In_Last_Expected_Due_Date:=FixedNextPaymentDate from tbl_loans where LoanID = 649;

SELECT @In_Pfreq_Code:=PayFrequency from tbl_loans where LoanID = 649;

-- **************************
SELECT @LV_Pfreq_Category_Code:=Pfreq_Category_Code FROM tbl_Pay_Frequency WHERE Pfreq_Code = @In_Pfreq_Code;

-- **************************

SELECT DATE_FORMAT(@Out_Next_Expected_Due_Date,'%w'),@In_Last_Expected_Due_Date,@Out_Next_Expected_Due_Date:=ADDDATE(@In_Last_Expected_Due_Date,8);
SELECT DATE_FORMAT(@Out_Next_Expected_Due_Date,'%w');
WHILE DATE_FORMAT(@Out_Next_Expected_Due_Date,'%w') != 1 DO
BEGIN
SELECT @Out_Next_Expected_Due_Date:=ADDDATE(@Out_Next_Expected_Due_Date,1);
END;
END WHILE;

[ERROR in query 6] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE DATE_FORMAT(@Out_Next_Expected_Due_Date,'%w') != 1 DO
BEGIN
SELECT @Out_' at line 1

Options: ReplyQuote


Subject
Views
Written By
Posted
Creating trigger succeeds, but variables not working
2013
March 30, 2012 09:41AM


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.