mySQL TRIGGER problem
 

Hello,

I'm having some sintax problems using PL/SQL in mySQL TRIGGERS.. I need some help please :).
Here is the code:

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

Re: mySQL TRIGGER problem
 

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

INSERT INTO table (...) VALUES (...) ON DUPLICATE KEY UPDATE ....

krs,
tesu