Auto Update Columns

[chewbears]

I have this query to convert file paths to image directory paths and Degree's minutes seconds to Decimal Degrees.

 

"select *, concat('mywebsitehere',path) as URL, (substr(EXIF_lat,1, locate('° ', EXIF_lat) - 1) + substr(EXIF_lat,locate('° ', EXIF_lat) + 7, locate('\'', EXIF_lat) - 1)/60 + substr(EXIF_lat,locate('\'', EXIF_lat) + 2,locate('\"', EXIF_lat) - 1)/3600) as Latitude, concat('-',(substr(EXIF_long,1,locate('° ', EXIF_long) - 1) + substr(EXIF_long, locate('° ',EXIF_long) + 7, locate('\'', EXIF_long) - 1)/60 + substr(EXIF_long, locate('\'', EXIF_long) + 2, locate('\"', EXIF_long) - 1)/3600)) as Longitude from plogger_pictures where EXIF_lat is not null and EXIF_long is not null;"

 

I want it to automatically do it whenever a record is added to the table.  So basically Latitude, Longitude, URL, would be autocreated and populated when a record is added.  How do I make this code, either all together or in pieces work to achieve my goal?

 

Thanks

[fenway]

Not sure I understand -- you want the update syntax, or a trigger?

[chewbears]

I want a trigger because currently I have it running in a cron, so every night at mid night I populate the old fields with the new fields using these lines.

 

Update TABLE_NAME_HERE 
SET Lat_DD = (substr(EXIF_lat,1, locate('° ', EXIF_lat) - 1) + substr(EXIF_lat,locate('° ', EXIF_lat) + 7, locate('\'', EXIF_lat) - 1)/60 + substr(EXIF_lat,locate('\'', EXIF_lat) + 2,locate('\"', EXIF_lat) - 1)/3600)

 

Was just wondering if there is a way to make the above happen on its own whenever a record is say added to the database.

[chewbears]

Where is the trigger stored?  Not really understanding the syntax

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

[chewbears]

Think I got it.  Found a MySQL book from a training class that answered it.  A trigger is just an object within the database.  Created by  giving it a name an event and then a statement.  Displayed by...  show triggers