Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL Trigger error

Posted on 2011-10-18
27
Medium Priority
?
461 Views
Last Modified: 2012-06-21
Hi all,

I am trying out this trigger:

create trigger triggerName
after insert, update on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
   WHERE id = OLD.id;
end

but phpmyadmin is giving me this error:

#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 on database1.table1
for each row
begin

UPDATE database2.Table1
   ' at line 2

Can anyone help please?

Thanks
0
Comment
Question by:error77
  • 13
  • 9
  • 5
27 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36989328
I think MySQL doesnt yet support triggers defined for multiple DML types.  Try creating own each for insert and for update.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989357
It is probably having both INSERT, UPDATE. I was probably thinking MS SQL syntax when if I wrote that or didn't correct it in your previous question.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989363
Ah, sorry johanntagle, I did not see your comment. I agree. It was an oversight from previous question.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:error77
ID: 36989436
It's something else...

Trying this:

create trigger triggerName
after insert on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
end

and getting:

#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 'end' at line 8

I'm running it on phpmyadmin

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989458
Okay, remember to change the DELIMITER.

DELIMITER $$

create trigger triggerName
after insert on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
end $$

DELIMITER ;
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989466
Note, you are setting every user_id in database2.table1 to the NEW.user_id. You probably want the WHERE clause you original had added back.
0
 

Author Comment

by:error77
ID: 36989535
OK, trying this now but no luck:

DELIMITER $$

create trigger triggerName
after insert on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
   WHERE id = OLD.id;
end $$

DELIMITER ;

This is what I've done:

I've created 2 databases (Same MySQL) ...

1. database1
2. database2

Each database has a table called "table1"

and each table has 2 fields:  id and user_id

I have then populated database1.table1 with 2 records.

And finally I am trying to create a trigger for the above.

I am using mySQL version 5.1.41

Hope all this helps.

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989607
What is the error you are getting? i.e., what does no luck mean?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36989618
See if it is just the ending delimiter:
(but maybe you want to INSERT on INSERT and then on the AFTER UPDATE trigger, UPDATE other table)
DELIMITER $$

create trigger triggerName
after insert on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
   WHERE id = OLD.id; 
end;
$$

DELIMITER ;

Open in new window

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36989674
If mwvisa1's latest post still doesn't work I would suggest trying it on the command line client - maybe it's phpmyadmin?
0
 

Author Comment

by:error77
ID: 36992459
It's now not giving me an error from the actual code it's basically timing out ...

Fatal error: Maximum execution time of 300 seconds exceeded in C:\wamp\apps\phpmyadmin3.2.0.1\libraries\import\sql.php on line 258

Is this supposed to be so hard to do? :o/

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36993032
No. Did you try from the command line?
0
 

Author Comment

by:error77
ID: 36994664
To be honest I don't know how to access the command line. I have an online host with CPanel. You've lost me on this one :o)
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36994763
Do you know what version of MySQL your host is running? How did you verify the two databases are on the same MySQL server. I.e., is it possible your host put one database on one server and one on another within their shared environment?
0
 

Author Comment

by:error77
ID: 36994865
I do everything using phpMyAdmin so until now I've never had use the command line. I know that all databases are in the same mySQL.

I've just tried this query on my localhost mySQL command line ... Says syntax error on this line:

WHERE id = OLD.id
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36995170
So to be clear (you executed the following from the command line):

1. Change delimiter to $$
DELIMITER $$

Open in new window


2. Ran create trigger DDL:
create trigger triggerName
after insert on database1.table1
for each row
begin

UPDATE database2.Table1
   SET user_id = NEW.user_id
   WHERE id = OLD.id; 
end;
$$

Open in new window


3. change delimiter back to ;
DELIMITER ;

Open in new window


And you got an error next to WHERE id = OLD.id; ?
0
 

Author Comment

by:error77
ID: 36995456
Yes, see the screenshot attached. Screenshot of problem
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 400 total points
ID: 36996012
That's odd.  You shouldgo back to mysql> after the delimiter command.  Can you try a different delimiterinstead of $$?  I always use | when I create triggers and procedures.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36996426
DELIMITER -- in your screen shot, you have DELIMETER.
0
 

Author Comment

by:error77
ID: 36996501
Tried all of the above but still not working..see screenshot.

 screenshot 2
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36996517
Again, the last end should be end; |
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36996534
To explain, DELIMITER | or DELIMITER $$ causes MySQL not to evaluate the statement until it sees the | or $$.  But the trigger creation statement should have the standard semicolons in the statements inside it and at the last closing END
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36996540
You have INSERT INTO there, so that is why it is not working with the WHERE clause.
0
 

Author Comment

by:error77
ID: 36996609
OK, got a different error now...see screenshot
 screenshot 3
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 1600 total points
ID: 36996639
Yes, that is for UPDATE.

Try these:
create trigger trg_table1_ins
after insert on database1.table1
for each row
begin

insert into database2.Table1(id, user_id)
values(NEW.id, NEW.user_id);
end;
|

Open in new window


create trigger trg_table1_upd
after update on database1.table1
for each row
begin

update database2.Table1
set user_id = NEW.user_id
where id = OLD.id;
end;
|

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36996662
And actually, your ID should not ever change so NEW.id can probably always be used to reduce confusion.
0
 

Author Closing Comment

by:error77
ID: 36996678
IT WORKED!!!! Thanks a million :o)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question