Issues creating trigger in MySQL 5.0.27 - SOLVED

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Issues creating trigger in MySQL 5.0.27 - SOLVED

Post 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.
Last edited by ReverendDexter on Tue Jun 19, 2007 4:22 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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>
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Perhaps some of the commands are case sensitive. Other than that, looks like it should work.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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;
|
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

That was it entirely.

Thank you ever so much!

-Dex
Post Reply