Trigger issues

[dele454]

Hi,

I have a wierd scenario happening here. i am currently using mysql version 5.0.51 locally for development and my domain uses 5.0.45. I was having this error while trying to create a trigger which i didnt have problems with creating locally:

DELIMITER //
CREATE TRIGGER au_mem_dj_grapher AFTER UPDATE
ON members
FOR EACH ROW
BEGIN

DECLARE who_isphoto INT;
DECLARE who_idphoto INT;
DECLARE who_isdj INT;
DECLARE who_idDj INT;

SELECT MemberID INTO who_idphoto FROM
members WHERE MemberID=OLD.MemberID AND IsPhotographer !=OLD.IsPhotographer;

SELECT IsPhotographer INTO who_isphoto
FROM Members WHERE
MemberID=who_idphoto;

IF who_isphoto = 1 THEN
INSERT INTO Photographers (PhotographerID, JoinDate) Values
(who_idphoto,NOW());

ELSEIF who_isphoto = 0  THEN
DELETE FROM Photographers WHERE PhotographerID = who_idphoto;
END IF;

SELECT MemberID INTO who_idDj FROM
members WHERE
MemberID=OLD.MemberID AND IsDJ!=OLD.IsDJ;

SELECT IsDJ INTO who_isdj FROM Members
WHERE MemberID=who_idDj;

IF who_isdj = 1 THEN
INSERT INTO DJs (DJID, JoinDate) Values
(who_idDj,NOW());

ELSEIF who_isdj = 0  THEN
DELETE FROM DJs WHERE DJID = who_idDj;
DELETE FROM EventDJ WHERE DJID = who_idDj;
END IF;
END
//

Error: ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

the wierd part is that i had the version of mysql upgraded to 5.0.67 on my domain but am still having this error. Why is this so??

[dele454]

Any help somebody? Am desperately in need of some assistance. Thanks

[longneck]

that simply means you already have a trigger defined for that table on the server. have you looked at what triggers you already have set up?

[dele454]

I thought that was the problem but i looked in my information_schema database > triggers table but couldnt find any triggers already defined. So if no triggers have been defined for that trigger type why is Mysql implying otherwise??

that simply means you already have a trigger defined for that table on the server. have you looked at what triggers you already have set up?

[dele454]

Any response?Just remembering. I had a recent upgrade from my host from mysql 5.0.45 to 5.0.67. Not sure if i had any triggers before the upgrade. But i deleted the database schema after the upgrade and recreated another one. Does this still hold, despite the fact that i CANT see any trigger defined for my db?

http://dev.mysql.com/doc/refman/5.0/...p-trigger.html

When upgrading from a previous version of MySQL 5.0 to MySQL 5.0.10 or newer, you must drop all triggers before upgrading and re-create them afterwards, or else DROP TRIGGER does not work after the upgrade

Thanks and looking forward to your feedback.

And running SELECT COUNT(*) FROM information_schema.TRIGGERS ; gives me 0. So i dont know what is happening!!!

[r937]

dele, please don't bump, it's against sitepoint rules

Don't Bump Threads

Sometimes, your thread just won't get the attention you hoped it would. Don't just make an empty post to the same thread to "bump" it back up to the top of the forum -- if no one responded to the content you posted the first time around, they're not likely to respond to that same information now, are they? The secret to getting a response to your question is to post more information -- flesh out the problem you're having, or the question you asked. The new information may well allow someone who couldn't participate before to join in the conversation.

[dele454]

Sorry Rudy, It wasnt my intention. I was typing a message to conclude on what i was saying but hit the submit button by mistake. Then i had to dash somewhere. But i have finished my comment

Thanks for notifying me on this

[dele454]

thanks sorted!!