Problem in creating triggers in mysql

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
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

Problem in creating triggers in mysql

Post by kalpesh »

hi i am trying to create trigger in mysql query browser.

But i get following error:
Script line: 1 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 temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','Tr' at line 4


This is my trigger:

CREATE TRIGGER temptri After insert on product
for each Row
begin
insert into temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True');
END$
delimiter$

Please help me .
Thanks in advance.
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Problem in creating triggers in mysql

Post by novice4eva »

MySql is not my forte but a quick check with the manual suggests doing this:

Code: Select all

 
delimiter$
CREATE TRIGGER temptri After insert on product
for each Row
begin
insert into temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True');
END;
$
 
PS: you might want to check the version too, triggers are supported from MySQL 5.0.2 onwards.
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

Re: Problem in creating triggers in mysql

Post by kalpesh »

I am trying trigger from phpmyadmin.
In that mysql version is 5.0.27.

I try u r code but it still not work;
It gives me following error:

Code: Select all

 
delimiter$
CREATE TRIGGER temptri After insert on product
 for each Row
 begin
 insert into temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True');
END;
 $
 
#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 'delimiter$
CREATE TRIGGER temptri After insert on product
for each Row
begi' at line 1
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Problem in creating triggers in mysql

Post by novice4eva »

I tried that code in mysql console and it worked well. If you can access console, it is a guaranteed workable code! :D

Ahh got it, instead of using delimiter$, there is this text field where you can set the delimiter right under the textbox where you write your trigger(IN PHPMYADMIN). There set the delimiter $ and execute your trigger without delimiter$ ... WORKED HERE :mrgreen:
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

Re: Problem in creating triggers in mysql

Post by kalpesh »

i still doesn't get u r idea. :oops:
i try the above code in mysql console and php myadmin.
I want to see what code u write in u r mysql console.
Please help me :banghead:
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Problem in creating triggers in mysql

Post by novice4eva »

IN MYSQL CONSOLE:

STEP 1 : delimiter $
STEP 2 :
CREATE TRIGGER temptri After insert on product
for each Row
begin
insert into temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True');
END;
$

IN PHPMYADMIN :
STEP 1 : SET DELIMITER as "$" in the text field
STEP 2 :
CREATE TRIGGER temptri After insert on product
for each Row
begin
insert into temp_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True');
END;
$


AND THIS IS WHERE YOU MIGHT HAVE MADE ERROR
while setting delimiter give an space
NOT delimiter$ BUT delimiter $
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

Re: Problem in creating triggers in mysql

Post by kalpesh »

Thanks novice4eva ,For being patient with me.
And i really thankful to u for solving my problem.
It works perfectly well.
thanks again.
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Problem in creating triggers in mysql

Post by novice4eva »

your're welcomed friend :drunk:
piyushtiwari
Forum Newbie
Posts: 5
Joined: Mon Nov 24, 2008 8:41 am

Re: Problem in creating triggers in mysql

Post by piyushtiwari »

Thanks but can anybody of tell u how exactly we set triggers in phpmyadmin like in which file we add the code for triggers. I had created 5 tables and want table 4 and 5 to be populated whenever a particular condition in first 3 tables is satisfied
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Problem in creating triggers in mysql

Post by novice4eva »

but where to write this code i had 5 tables i want to set a trigger so that whenever some condition in table 1,2,3 is satisfied tuples in table 4 and 5 should get populated
According to my knowledge you can fire a trigger when certain insert/update/delete is done on a table. You sound like you want to check in more than one table for a trigger to execute?? And yes as for the question in your PM,
Ya same is for me what i want to ask is if i ad trigger over there and i was able to do will it exist forever or not since suppose i want to associate trigger with table 3 and i do it as we were saying will it work forever or i need to do run trigger each time
You don't need to fret about it, yes after you create a trigger for your table "3" IT WILL EXECUTE for what it has been defined for ie: before/after insert/update/delete on table "3" automatically as per the event you defined the trigger for. To give you brief idea, as in the previous examples in the POST, you can see the trigger as

Code: Select all

 
CREATE TRIGGER temptri After insert on product
for each Row
begin
 
this means that the trigger will be executed for each insert operation done in table "product"
Post Reply