Click here to Skip to main content
14,869,389 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE TRIGGER tr BEFORE UPDATE ON batch_dat_trans FOR EACH ROW
BEGIN

  DECLARE @result_position int;
  DECLARE @result_all int;
  DECLARE @proud double;
  DECLARE @date1 DATE;
  DECLARE @cust varchar(100);
  SET @result_position = 1;

  DELETE FROM temp;
  SELECT COUNT(@Batch_Date) INTO @result_all FROM batch_dat_trans WHERE Batch_date BETWEEN DATE_SUB(NOW(), INTERVAL 45 DAY) AND NOW() ;

  lab: LOOP

    SELECT Batch_Date,Customer_Code,Production_Qty INTO @date1,@cust,@proud FROM batch_dat_trans WHERE Batch_date BETWEEN DATE_SUB(NOW(), INTERVAL 25 DAY) AND NOW() LIMIT @result_position,1 ;
    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
Posted
Updated 13-May-13 22:33pm
v3
Comments
E.F. Nijboer 14-May-13 4:04am
   
Well... what is it? We cannot see the errors you get on your screen. Please improve your question and add the error you get.
vishal.v.patil 14-May-13 4:39am
   
I GOT ERROR...........>>

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 '@result_position int;
DECLARE @result_all int;
DECLARE @proud double;
D' at line 4

1 solution

   
Comments
vishal.v.patil 14-May-13 4:56am
   
sorry its not working.....
Zoltán Zörgő 14-May-13 5:00am
   
What is not working? What is the exact command you want to issue? Are you using phpmyadmin? Your original code was without delimiter. How exactly have you added it?
vishal.v.patil 14-May-13 5:11am
   
no m using third party sql query browser....
Zoltán Zörgő 14-May-13 5:18am
   
Does not actually matter. I repeat: how exactly have you added the delimiter?
vishal.v.patil 14-May-13 5:20am
   
ok bro thnks for u r helpful rply..!!
Zoltán Zörgő 14-May-13 5:22am
   
It is solved than? If so, feel free to accept my answer.

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




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