SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Trigger issues

  1. #1
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trigger issues

    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:

    Code:
    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??

  2. #2
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any help somebody? Am desperately in need of some assistance. Thanks

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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??

    Quote Originally Posted by longneck View Post
    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?

  5. #5
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!!!
    Last edited by dele454; Dec 8, 2008 at 11:01.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    dele, please don't bump, it's against sitepoint rules
    Quote Originally Posted by da FAQ

    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks sorted!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •