Click here to Skip to main content
15,939,853 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
DELIMITER //
CREATE TRIGGER `test11` BEFORE UPDATE ON batch FOR EACH ROW
BEGIN
  DECLARE proud Double;
  DECLARE result_position int;
  DECLARE result_all int;

  DECLARE date1 date;
  DECLARE cust varchar(100);
  SET @result_position = 1;   
 
  DELETE FROM temp;
  SELECT COUNT(Batch_Date) INTO @result_all FROM batch WHERE Batch_date BETWEEN DATE_SUB(NOW(), INTERVAL 25 DAY) AND NOW() ;
         
  lab: LOOP
    PREPARE STMT FROM 'SELECT Batch_Date,Customer_Code,Production_Qty INTO @date1,@cust,@proud FROM batch WHERE Batch_date BETWEEN DATE_SUB(NOW(), INTERVAL 25 DAY) AND NOW() LIMIT ? , 1' ;
    EXECUTE STMT USING @result_position ;

    INSERT INTO temp (Batch_Date,Customer_Code,Production_Qty) VALUES(@date1,@cust,@proud);
    SET @result_position = @result_position + 1;

    if @result_position  > @result_all then
     LEAVE  lab;
    end if;
  END LOOP;

  END
  //

DELIMITER ;
Posted
Updated 21-May-13 21:36pm
v2
Comments
vishal.v.patil 22-May-13 4:14am    
still same error...

1 solution

Hello Vishal,

The Current MySQL (5.7) Manual section D.1 Restrictions on Stored Programs states that

  1. SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
  2. Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements .
  3. Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope.

So you can see that it's not permitted.

Regards,
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900