I have a cron job that runs frequently that I may be able to replace with a trigger, but not sure if this is possible.

I'm using mysql version 5.1.56

I need two things to happen on insert and update, but not sure if it should be before or after.

First, if the field real_user_id is blank (or default 0) I want to update it to the same as field user_id

Next, if the field real_user_id is not equal to the field user_id I want to update the field user_id to the same as field real_user_id AND update the field username to a field in a table in another database on the same server.

The sql user has read permission to the other database on the server.

Currently I achieve this with a cron that looks like this:

// fisrt pass, update missing real_user_id
$sql = "UPDATE [my_table] SET real_user_id = user_id WHERE real_user_id = 0";
$result = mysql_query($sql, $link);
echo mysql_affected_rows($link) . " rows affected with real_user_id update<br />\n";

// second pass, update real user_id & user_names
$sql = "SELECT id, real_user_id FROM [my_table] WHERE user_id <> real_user_id";
$result = mysql_query($sql, $link);
while($row=mysql_fetch_array($result)) {
	// get real user name
	$sql = "SELECT username FROM [my_other_database].[my_other_table] WHERE user_id = " . $row['real_user_id'];
	$username_result = mysql_query($sql, $link);
	if ($username_row = mysql_fetch_array($username_result) ) {
		// update [my_table] to correct user_id and user_name
		$sql = "UPDATE [my_table] SET user_id = real_user_id, author = '" . $username_row['username'] . "' WHERE id = " . $row['id'];
		$authorupdate_result = mysql_query($sql, $link);
		if (mysql_affected_rows($link) > 0) {
			echo $sql . "<br />\n";
Can a trigger read the data from another database on the same server?

If not, can a UDF do this and the trigger call the udf?

I've used triggers sparingly on MS SQL but never MySQL and it would be great if I didn't have to run this cron just in case something has changed.