功能/触发器已在使用中？

Question

Im having problems getting an update function to work. The function marks badges as seen so that they are hidden from a notification window.

The function is called when the user clicks a button to mark them as seen.

I have two triggers on the table its trying to update which I think may be causing the problem.

The problem is : Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Triggers:

Function:

function markAsSeen() {
        require "connect.php"; 

    $seen = mysqli_query($connection,"Update userbadges 
INNER JOIN users ON users.id = userbadges.user_id
SET seen='1'
WHERE studentid = '".$_SESSION["studentid"]."' && seen=0")  or die(mysqli_error($connection));

  while ($data = mysqli_fetch_array($seen)) {

echo 'Done';


  }
}

Is there any way around this?

Answer 1

Your issue is that the update_users_trigger trigger makes changes to the contents of the table users, while the query that is triggering the execution of this trigger also uses the table users.

You will need to adjust your query so that this deadlock doesn't occur. It isn't clear which fields are from each table, but I suspect that in your initial query you need to join on users so that you can query on studentid.

You could create a different function to get the userID that you need something like the following:

require_once "connect.php";
function getUserIDFromStudentID($student_id, mysqli $connection)
{
    $query = 'SELECT id FROM users WHERE studentid = ? LIMIT 1';
    $stmt = $connection->prepare($query);
    // Replace the below s to an i if it's supposed to be an integer
    $stmt->bind_param("s", $student_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $record = $result->fetch_object();
    $result->free();
    if ($record) {
        return $record->id;
    }
}

function markAsSeen(mysqli $connection) {
    $user_id = getUserIDFromStudentID($_SESSION["studentid"], $connection);
    if (! $user_id) {
        throw new Exception('Unable to get user id');
    }
    $seen_query = 'UPDATE userbadges SET seen = 1 WHERE user_id = ? and seen = 0';
    $stmt = $connection->prepare($seen_query);
    // Replace the below s to an i if it's supposed to be an integer
    $stmt->bind_param("s", $user_id);
    $result = $stmt->execute();
    if (! $result) {
        die(mysqli_error($connection));
    }
    echo 'Done';
}

Passing the connection object around rather than requiring a global file to be required every time will allow for more flexibility.