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 />\
";
// 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 />\
";
}
}
}
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.