MySQL

mySQL TRIGGER problem

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2008

Posts: 1

Reputation: M3nTaL is an unknown quantity at this point

Solved Threads: 0

M3nTaL

Offline

Newbie Poster

mySQL TRIGGER problem

May 8th, 2008

Hello,

I'm having some sintax problems using PL/SQL in mySQL TRIGGERS.. I need some help please

.
Here is the code:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CREATE TRIGGER modificou
   AFTER UPDATE ON monitor
   FOR EACH ROW
 BEGIN
   IF NOT EXISTS(SELECT estado_tmp FROM alteracoes WHERE id_tmp = OLD.id)
   THEN
       INSERT INTO alteracoes VALUES(OLD.id,NEW.estado);
   ELSE
       UPDATE alteracoes SET estado_tmp = NEW.estado WHERE id_tmp = OLD.id;
   END IF;
 END modificou;
CREATE TRIGGER modificou
   AFTER UPDATE ON monitor
   FOR EACH ROW
 BEGIN
   IF NOT EXISTS(SELECT estado_tmp FROM alteracoes WHERE id_tmp = OLD.id)
   THEN
       INSERT INTO alteracoes VALUES(OLD.id,NEW.estado);
   ELSE
       UPDATE alteracoes SET estado_tmp = NEW.estado WHERE id_tmp = OLD.id;
   END IF;
 END modificou;

What could be wrong?? For testing I'm using phpmyadmin 2.11.1

Thanks

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: mySQL TRIGGER problem

May 9th, 2008

hi,
what is the error message?

Your trigger will be fired AFTER update, and, obviously, it were to catch update errors IF the record to be updated does not exist in your table. Well, if that record does not exist, update would not be execute, therefore (i believe so

) AFTER-update trigger will never be fired.

If you want to prevent failure, if a record to be inserted already exists (that would lead into duplicate primary key), you might use

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO table (...) VALUES (...) ON DUPLICATE KEY UPDATE ....
INSERT INTO table (...) VALUES (...) ON DUPLICATE KEY UPDATE ....