Trigger working with server 5.0.51 but not 5.0.22

--00504502d7cd25e8ab0469c7176d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I
tried to move this to a production server (version 5.0.22 fedora) I ran into
2 problems:

1. I try to install the trigger from a text file, and the first lines were:
DELIMITER $$
DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos;
CREATE TRIGGER cfe_tg_calcular_consumos
AFTER INSERT ON cfe_lecturas_tiendas
but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of
course I use this text file at my development environment without problem.
And at production server I need to delete the line DROP TRIGGER ... to
create the trigger.

2. This trigger create a temporary table and then call a stored procedure
wich does some arithmetic and put the result in the temporary table. When
the trigger get fired at the production server, I got *"**SQL Error:* 1146:
Table 'filasPOS.tmp_calculos_res' doesn't exist"

Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or
I missing something? Thanks in advance

--
Mauricio Tellez

--00504502d7cd25e8ab0469c7176d--

Re: Trigger working with server 5.0.51 but not 5.0.22

2009/5/13 Mauricio Tellez :
> Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I
> tried to move this to a production server (version 5.0.22 fedora) I ran into
[...]
> but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of

http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html
"The IF EXISTS clause was added in MySQL 5.0.32."

Greetings,

mattia.

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

RE: Trigger working with server 5.0.51 but not 5.0.22

The DROP TRIGGER command is in the wrong place. You changed the delimiter t=
o $$ but still tried to use the semicolon(;) with DROP TRIGGER.

Your code should read like this:
DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos;
DELIMITER $$
CREATE TRIGGER cfe_tg_calcular_consumos
AFTER INSERT ON cfe_lecturas_tiendas

Or change the ; to $$ on the DROP TRIGGER command like this:
DELIMITER $$
DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos $$
CREATE TRIGGER cfe_tg_calcular_consumos
AFTER INSERT ON cfe_lecturas_tiendas

Give it a try !!!

Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM : RolandoLogicWorx
Skype : RolandoLogicWorx
redwards@logicworks.net


-----Original Message-----
From: Mauricio Tellez [mailto:mauricio.tellez@gmail.com]=20
Sent: Wednesday, May 13, 2009 4:35 AM
To: mysql@lists.mysql.com
Subject: Trigger working with server 5.0.51 but not 5.0.22

Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I
tried to move this to a production server (version 5.0.22 fedora) I ran int=
o
2 problems:

1. I try to install the trigger from a text file, and the first lines were:
DELIMITER $$
DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos;
CREATE TRIGGER cfe_tg_calcular_consumos
AFTER INSERT ON cfe_lecturas_tiendas
but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of
course I use this text file at my development environment without problem.
And at production server I need to delete the line DROP TRIGGER ... to
create the trigger.

2. This trigger create a temporary table and then call a stored procedure
wich does some arithmetic and put the result in the temporary table. When
the trigger get fired at the production server, I got *"**SQL Error:* 1146=
:
Table 'filasPOS.tmp_calculos_res' doesn't exist"

Is there a big change in handling triggers from version 5.0.22 to 5.0.51? O=
r
I missing something? Thanks in advance

--=20
Mauricio Tellez

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg