2

i'm trying to write a mySQL tigger but i can't put my code in phpMyAdmin MySQL without getting a syntax error. can someone help me please?

Tables :

jeu

My trigger :

CREATE TRIGGER after_jeu_insert ON jeu
   AFTER INSERT
   AS
    BEGIN
        DECLARE @gameid int, @oldTurn int, @newTurn int
        SELECT @gameid=idpartie FROM INSERTED
        SELECT @oldturn=tour FROM partie WHERE idpartie=@gameid
        IF(@oldTurn IS NULL)
        {
            SET @newTurn = 1
        }
        ELSE
        {
            IF(@oldTurn==1)
                SET @newTurn = 2
            ELSE
                SET @newTurn = 1
        }
        UPDATE partie SET tour = @newTurn, derniercoup = NOW() WHERE idpartie = @gameid
    END

I don't find the error, if someone could help me it would be very nice. Thanks

1
  • post your table definitions here sqlfiddle.com Commented Jun 1, 2014 at 11:26

1 Answer 1

2

There are many errors to correct.

  1. No end of statements.
  2. No order of syntax followed.
  3. Declaration of variables is not correct.
  4. Selection in to variables from a column is not correct.
  5. Brace based clocks is not supported.
  6. Does table inserted contain only one row? Otherwise you need a where clause or limit.
  7. etc.

You better work more to learn.
Please refer to Trigger Syntax and Examples for better understanding.

Change your code as follows and it may work if all is well on your database objects.

drop trigger if exists after_jeu_insert;

delimiter //

CREATE TRIGGER after_jeu_insert after insert ON jeu for each row
BEGIN
    DECLARE _game_id int;
    DECLARE _old_turn int;
    DECLARE _new_turn int;

    -- following line may not require limit clause
    -- if used proper where condition.
    SELECT idpartie into _game_id FROM INSERTED limit 1; 

    SELECT tour into _old_turn FROM partie WHERE idpartie = _game_id;

    IF _old_turn IS NULL then
        SET _new_turn = 1;
    ELSIF _old_turn = 1 then
        SET _new_turn = 2;
    ELSE
        SET _new_turn = 1;
    END IF;

    UPDATE partie 
       SET tour = _new_turn
         , derniercoup = NOW()
     WHERE idpartie = _game_id;
END;
//

delimiter;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.