1

The program I am working on has a poorly designed backend and basically there are two different tables needed to hold the exact same information. I am trying to write a trigger that will update table B with the same information that was just updated in table A. Oh and this is using a MYSQL database I am not sure if I am just having a syntax error or if I am missing concepts, any help would be much appreciated... here is what I have as of yet

DELIMITER $$         
DROP TRIGGER IF EXISTS after_update_A;

CREATE TRIGGER `after_update_A` 

    AFTER UPDATE ON `A`  FOR EACH ROW
    BEGIN
        UPDATE TABLE B
        SET  username = NEW.username
           , password = NEW.password
           , email = NEW.email
        WHERE id = NEW.id
    END

    $$
DELIMITER ;

And the errors I get are all some what similar to this... ERROR 1064 (42000): 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 '= users.username , B.email = users.email D' at line 4

letsgettechnical
  • 73
  • 1
  • 4
  • 11

2 Answers2

5

You need to close every statement inside the trigger with a ;, and I do mean every.

CREATE TRIGGER `after_update_A` AFTER UPDATE ON `A` FOR EACH ROW
BEGIN
    UPDATE TABLE B
    SET  username = NEW.username
       , password = NEW.password
       , email = NEW.email
    WHERE id = NEW.id;    //<<-----------
END $$
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Thanks, that is probably one of the problems, but not the fix to this one. Before the error it is also showing me 'Display all 939 possibilities? (y or n)' and proceeding to show all of them, and I am not sure what those are or why they are being shown. – letsgettechnical Sep 13 '11 at 16:02
  • `Display all 939 possibilities? (y or n)` sounds like a unix-shell prompt, not like something MySQL would say. – Johan Sep 13 '11 at 16:11
0

I have the feeling that you are compiling .sql as .sh

the trigger is .sql and should be executed using

mysql-batch-commands.html

p.s. What @Johan says is obviously correct as well

p.s.2 Now I see another error: you will need a delimiter between the drop and create statements

ninjabber
  • 371
  • 2
  • 7