Page 1 of 1

Issues creating trigger in MySQL 5.0.27 - SOLVED

Posted: Tue Jun 19, 2007 11:41 am
by ReverendDexter
I'm having issues creating the following trigger:

Code: Select all

create trigger login_to_con after insert on login
    for each row begin
    insert into contact set con_name = new.co_name;
end;
If I enter it into the MySQL console, I get this error:

Code: Select all

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 'insert
 into contact set con_name = new.co_name' at line 3
I built that code based on the example used on what I found in the MySQL docs here: http://dev.mysql.com/doc/refman/5.0/en/ ... igger.html

Near as I can tell, my code is identical to their example with the exception of table names... any ideas?

If it helps, here are the table definitions:

login:

Code: Select all

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| co_name     | varchar(255) | NO   | PRI |         |       |
| pass        | varchar(255) | NO   |     |         |       |
| admin_email | varchar(255) | NO   |     |         |       |
+-------------+--------------+------+-----+---------+-------+
contact:

Code: Select all

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| con_id   | int(10) unsigned | NO   | PRI |         |       |
| con_name | varchar(255)     | NO   | MUL |         |       |
| email    | varchar(255)     | YES  |     | NULL    |       |
| phone    | varchar(15)      | YES  |     | NULL    |       |
| fax      | varchar(15)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+


-Dex

EDIT:
If I can't get this trigger to work, I'm just gonna end up doing the equivalent in PHP. I'd rather have the database take care of it, though.

Posted: Tue Jun 19, 2007 1:24 pm
by Benjamin
If the syntax IS the same, there may possibly be a reserved keyword in there.

See http://dev.mysql.com/doc/refman/5.0/en/ ... words.html

Posted: Tue Jun 19, 2007 1:48 pm
by ReverendDexter
I just ended up doing the equivalent in PHP, but I'm still interested in why this trigger fails to be created. I checked that list of reserved words, and I'm not seeing anything I'm using. (I also think that MySQL's documentation isn't written well, especially the examples. My feelings about poorly written examples extends to most documentation I've seen.).

I know there is some discussion of using the BEFORE vs. AFTER timeframe for the insert, but this is a foreign key situation, so the insert into contact *has* to be done after the insert into login, or it will violate the foreign key constratint.

-Dex

<pointless aside> Chalk up one more reason that I've favored Postgres in my limited experience with FOSS DBMSs... </pointless aside>

Posted: Tue Jun 19, 2007 2:13 pm
by Benjamin
Perhaps some of the commands are case sensitive. Other than that, looks like it should work.

Posted: Tue Jun 19, 2007 3:53 pm
by feyd
Line delimiter needs to change, I would say.

Note their example:

Code: Select all

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

Posted: Tue Jun 19, 2007 4:22 pm
by ReverendDexter
That was it entirely.

Thank you ever so much!

-Dex