0

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

2
Contributors
1
Reply
3
Views
9 Years
Discussion Span
Last Post by tesuji
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.