update query

hi,

i have two tables marker and location.if any any changes made in the location.it also effect in the markers table.i need the query for this operation.

thanks

 

In location table have four fields name,street,latitude and longitude.similarly marker table have four fields name,address,lat,lng.whenever new data insert or update made in the location table.it also effect marker table.
For perform above opertion i need query.if u know plz share with me

 

It sounds like you might want to rethink your database structure.
Having the same data in 2 tables is a waste and not very good structure; you should look at creating a foreign key between the 2 tables instead.

(I.E 'marker' references the data in the location table through its unique ID)

If you really must do it your way, you can do it as a trigger with no other code needed.

E.G

CREATE TRIGGER yourtriggername AFTER INSERT ON location
FOR EACH ROW BEGIN
	INSERT INTO marker (name, street, latitude, longtitude) VALUES (NEW.name, NEW.street, NEW.latitude, NEW.longtitude);
END;

Code (markup):

However, I strongly suggest you restructure your database instead.

 

i dont have any experiance in how to use triggers.plz explain how to use triggers in mysql.

thanks

 

Have you looked into changing your database structure? I highly advise you to do this nistead of going duplicating data in different tables.

It is standard practice to do this and you should be looking at it instead

You just need to do it from an SQL prompt (you may need to set a different delimeter, using: 'delimeter |' and then end the statement with that), or from a phpmyadmin/frontend. (You will need the SUPER privelege to set it up)

Note: I made a mistake in the trigger, fixed the syntax.

CREATE TRIGGER aaa AFTER INSERT ON location
FOR EACH ROW BEGIN
	INSERT INTO marker SET `name`=NEW.name, `street`=NEW.street, `lat`=NEW.lat, `long`=NEW.long;
END;

Code (markup):

 

actually i need to display the data from markers table not from location table but both have same data .For this requirement how i change the data structure.
come to trigger
i try to execute by using phpmyadmin i got error like this

SQL query:

CREATE TRIGGER aaa AFTER INSERT ON location
FOR EACH
ROW BEGIN
INSERT INTO marker
SET `name` = NEW.name,
`street` = NEW.street,
`latitude` = NEW.lat,
`longitude` = NEW.lng;

MySQL said: Documentation
#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 '' at line 3

guide to resolve it

 

What is the current structure of both tables, and what are their purposes? I.E What are you using them for?

You are missing an 'END;' statement in there. (You may also need to change the delimeter)