CODEWITHSUNDEEP
mysql
Fabio
Fabio

Reputation: 180

Trigger AFTER update syntax error

Im trying to build a simple trigger after update that will update another table, but I can't find the syntax error here. To do this I'm usign the phpMyAdmin wizard.

The error:

MySQL: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE question SET question.fname_author = new.fname, question.lname_au' at line 4

TABLES STRUCTURE:

  question             user_question         users

 - id PK               -id_user PFK         -id  PK
 - fname_author        -id_question PFK     -fname
 - lname_author        -fname_author FK     -lname 
 - question            -lname_author FK     -email  
 - category            -question FK         -office
 - level               -office FK           -password
 - created_at          -created_at          -created_at 
 - updated_at          -updated_at          -updated_at

CREATE TRIGGER `TAU_users_UPDATE_dati_domanda` AFTER UPDATE ON `users` 
FOR EACH ROW
     BEGIN
            SET @id_question = (SELECT user_question.id_question
                               FROM user_question
                               WHERE user_question.id_user =new.id);
            UPDATE question
            SET
                fname_author = new.fname,
                lname_author = new.lname
        WHERE question.id = @id_question
END;

Upvotes: 0

Views: 111

Answers (2)

Ryan Vincent
Ryan Vincent

Reputation: 4513

The answer provide by @Renzo should work...

I was working on testing the o/p code here with some test data ... as usual too late...

Whatever, I will post it anyway as it works...

So I created an SQLFddle with some test data.

Trigger Code: 

CREATE TRIGGER `TAU_users_UPDATE_dati_domanda` AFTER UPDATE ON `users` 
FOR EACH ROW
     BEGIN
            SET @id_question = (SELECT user_question.id_question
                               FROM user_question
                               WHERE user_question.id_user =new.id);

            UPDATE question
            SET
                question.fname_author = new.fname,
                question.lname_author = new.lname
        WHERE question.id = @id_question;
END;//

Not important - note: qualified column names in the update.

Test data before:

Users:

id  fname             lname             email           office      password Created_at
1   fname_u1          lname_u1          [email protected]     office_u1   pwd_u1   September, 17 2015 11:57:11
2   fname_u2          lname_u2          [email protected]     office_u2   pwd_u2   September, 17 2015 11:57:12

Question:

id  fname_author      lname_author      question           category level created_at
1   fname_q1          lname_q1          what question q1?  cat_1    1     September, 17 2015 11:57:12
2   fname_u2          lname_u2          what question q2?  cat_2    2     September, 17 2015 11:57:12

Update Statement:

UPDATE  users 
SET users.fname = 'fname_u2_new_fn01',
    users.lname = 'lname_u2_new_fn01'
WHERE users.id = 2//

Test Data After Update:

Users:

id  fname             lname             email           office      password Created_at
1   fname_u1          lname_u1          [email protected]     office_u1   pwd_u1   September, 17 2015 11:57:11
2   fname_u2_new_fn01 lname_u2_new_fn01 [email protected]     office_u2   pwd_u2   September, 17 2015 11:57:12

Question:

id  fname_author      lname_author      question           category level created_at
1   fname_q1          lname_q1          what question q1?  cat_1    1     September, 17 2015 11:57:12
2   fname_u2_new_fn01 lname_u2_new_fn01 what question q2?  cat_2    2     September, 17 2015 11:57:12

Upvotes: 1

Renzo
Renzo

Reputation: 27424

Try to change:

CREATE TRIGGER `TAU_users_UPDATE_dati_domanda` AFTER UPDATE ON `users` 
FOR EACH ROW
     BEGIN
            SET @id_question = (SELECT user_question.id_question
                               FROM user_question
                               WHERE user_question.id_user =new.id)
            UPDATE question
            SET
                question.fname_author = new.fname,
                question.lname_author = new.lname


        WHERE question.id = @id_question
END;

in:

delimiter //
CREATE TRIGGER `TAU_users_UPDATE_dati_domanda` AFTER UPDATE ON `users` 
FOR EACH ROW
     BEGIN
            SET @id_question = (SELECT user_question.id_question
                               FROM user_question
                               WHERE user_question.id_user =new.id);
            UPDATE question
            SET
                fname_author = new.fname,
                lname_author = new.lname
        WHERE question.id = @id_question;
END;//
delimiter ;

For the syntax and example of triggers, see: http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html and the relevant comment about the use of BEGIN ... END inside triggers.

Upvotes: 0

Related Questions