mySQL TRIGGER problem

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 363,805 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,601 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser:

Thread Tools Display Modes

Views: 696 | Replies: 1

•

Join Date: May 2008

Posts: 1

Reputation: M3nTaL is an unknown quantity at this point

Rep Power: 0

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

(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;

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

Thanks

•

Join Date: Apr 2008

Posts: 249

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 30

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 ....