Problem creating a Trigger

[anniyan]

Hi all,

When I am trying to create a trigger I get an error message which I am unable to comprehend.

create trigger updatepricech
 after insert on tbl_prices for each row
BEGIN
  DECLARE closep INTEGER;


select close into closep from temptable where Ticker = new.Ticker;
update tbl_prices set PriceCh = (Close - closep) where Ticker = new.Ticker and Trade_date = new.Trade_date;
end;


I get the error :

#1064 - 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 '' at line 4

Here line 4 is the declaration part.

Can you please let me know what could be wrong in this query?

[Rolando Edwards (DBA)]

Did you remember to switch your SQL delimiter ?
   
  MySQL default delimter for SQL is the semicolon ( ; )
  MySQL's stored procedure language also uses the semicolon to delimter statements.
   
  These two rules cannot peacefully coexist.
  You can get around this in three steps:
   
  1) Change you default SQL limiter to $$
  2) Create the Stored Procedure using END $$ instead of END;
  3) Change you default SQL limiter back to ;
   
  Like This:
   
  DELIMITER $$
create trigger updatepricech
after insert on tbl_prices for each row
BEGIN
DECLARE closep INTEGER;
select close into closep from temptable where Ticker = new.Ticker;
update tbl_prices set PriceCh = (Close - closep) where Ticker = new.Ticker
and Trade_date = new.Trade_date;
END $$
  DELIMITER ;
   
  Hey, give it a try !!!
 
anniyan <sriram.s@...> wrote:
 
Hi all,

When I am trying to create a trigger I get an error message which I am
unable to comprehend.

create trigger updatepricech
after insert on tbl_prices for each row
BEGIN
DECLARE closep INTEGER;


select close into closep from temptable where Ticker = new.Ticker;
update tbl_prices set PriceCh = (Close - closep) where Ticker = new.Ticker
and Trade_date = new.Trade_date;
end;


I get the error :

#1064 - 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 ''
at line 4

Here line 4 is the declaration part.

Can you please let me know what could be wrong in this query?
--
View this message in context: http://www.nabble.com/Problem-creating-a-Trigger-tf4392021.html#a12522149
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=rolando_mysql_dba@...



       
---------------------------------
Building a website is a piece of cake.
Yahoo! Small Business gives you all the tools to get online.

[anniyan]

Hi,
Thank you so much for the prompt response. Those comments were good to be kept in mind always.
I actually realised the mistake I was making as soon as I posted the problem here. So I managed the solve the problem.
However I have another problem which I need your ideas.
I have a table where prices for a few stock tickers are inserted. I have another temp table which holds the prices for the max trade date. I have a trigger which updates the temp table when there is a an insert into the main prices table. This makes sure that I have the latest data for all the tickers in the temp table.

Now I have a column called pricechange. I would like to calculate the difference between pevious close price and today's close price in that column. So theoretically speaking after the insert of new prices in the prices table and I would like a trigger that will update the pricechange with the price difference.
But I am unable to do it.

DBD::mysql::st execute failed: Can't update table 'tbl_prices' in stored functio
n/trigger because it is already used by statement which invoked this stored func

pls advice

[Martijn Tonies]

Hi,

> However I have another problem which I need your ideas.

It's better to create a new thread when you want to discuss a new
problem.

> I have a table where prices for a few stock tickers are inserted. I have
> another temp table which holds the prices for the max trade date. I have a
> trigger which updates the temp table when there is a an insert into the
main

> prices table. This makes sure that I have the latest data for all the
> tickers in the temp table.
>
> Now I have a column called pricechange. I would like to calculate the
> difference between pevious close price and today's close price in that
> column. So theoretically speaking after the insert of new prices in the
> prices table and I would like a trigger that will update the pricechange
> with the price difference.
> But I am unable to do it.
>
> DBD::mysql::st execute failed: Can't update table 'tbl_prices' in stored
> functio
> n/trigger because it is already used by statement which invoked this

stored
> func

Welcome to MySQLs half-assed trigger implementation ;-)

Can you do this from the first trigger that updates the temp table (inserts
the items?) instead?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...