[SOLVED] MySQL trigger trouble

[ConstantinL]

Hello

 

I am buiding a data base using MySQL 5.1 and i am having trouble creating a trigger.

I did the same thing in Microsoft SQL Server 2005, it worker perfectly, but now i have to use MySQL and i'm having trouble with it.

 

I have a table tab with the structure    id(autoincrement int), name(varchar 50), monitor(int set to 1 if i vant to monitor that row or 0 if not)

 

I want to create a trigger that will help me monitor the updates performed on this table. I want the trigger to capture the row that has been updated and inserted it into the monitortab table wich has an almost identical structure to the tab table.

Like i said i already did this in Microsoft SQL Server 2005 and it worked

 

I used the manual to build a statement to create the triger. This is the statement:

Create TRIGGER updatemonitor
after UPDATE
ON tab FOR EACH ROW
BEGIN
insert into monitortab set name=NEW.name where monitor=1;
END

 

I get this error mesage:  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 'where monitor=1' at line 5    -    error code 1064

 

I tried everything, even the statement that worked in Microsoft SQL Server 2005. I am lost.

 

Does anyone have an ideea, can anyone hel?

 

[gassaz]

Did you check the syntax for Mysql Insert Statement??

[ConstantinL]

Hello,

 

Thaks for the reply.

 

I did check the syntax, I based my statement on the example in the reference documentation at

 

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

 

I even tried

INSERT INTO monitorTab (name) VALUES ('asd') 

but i still get the error message

 

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 '' at line 5

 

Thanks again for the reply, if you have any other ideas pleas let me know.

[ConstantinL]

Problem solved.

 

I just had to make a script out of my statement and surround it by

 

delimiter |
                    statement
|

delimiter ;

 

Thanks for the interest shown

[ConstantinL]

How do i mark the topic as solved?

[fenway]

"Topic solved" button right above the quick reply area.