Update from another table

[Matt H]

(Posted on Triggers forum as well)

I log incoming file arrivals to a database logging the filename, shortcode and timestamp. I Then take the shortcode and look it up in another table, which gives me the file deadline time. I then want to run TIMEDIFF against the 2 time values to determine if the files are late or on time, and log the difference into a collumn, f_Time_Diff.

I log the arrival time using a trigger. However, since triggers can't access other tables, I presume a stored procedure is needed, but I can't get that running now. It needs to be something like:

UPDATE tf_filetimes, td_publication
SET f_Time_Diff = TIMEDIFF(f_Time,Deadline)
WHERE tf_filetimes.shortcode = td_publication.shortcode

But it doesn't work. Can anyone offer any advice? It needs to run each time an entry is added, on a row-by-row basis. We get around 2-3 files every minute - more or less - 24 hours a day, 6 days a week.