Functioning of MySQL Session variables in trigger

Question

I have two entities (shown below): 1. User 2. Books

Now, I have got a task to record activity in "log table" each time a user deletes a book. The schema for book and log table is as follows:

book table:

id      book
--      ----
1        B1
2        B2
3        B3
4        B4

log table:

user    record_id     record_type    action
----    ---------     -----------    ------
12          7            book        delete

For a user to delete a book, he needs to be logged in the system. So once a user logs in the system I get its USERID and store it in a PHP session and in MySQL session variable.

$_SESSION['user_id'] = $user_id;
mysqli_query("SET @user_id = $user_id");  //I am planning to use this value in my mysql trigger.

Now, to store the entries in log table I am using the below shown trigger.

DROP TRIGGER IF EXISTS `book_delete`;
CREATE TRIGGER book_delete AFTER DELETE ON books
FOR EACH ROW
BEGIN
    INSERT INTO log_table(`user`, `record_id`, `record_type`, `action`)
    VALUES (@user_id, OLD.`books.id`, 'book','delete');
END;

My concerns are:

a. Whether I can trust the MySQL variable (i.e @user_id) in my trigger or not?

b. Will the @user_id used in trigger will always have value that was set when user was logged in?

c. What will happen when multiple users logs in at the same time?

Please help.

Luigi Cangiano · Accepted Answer · 2014-10-03 13:37:19Z

Each PHP page can use one or more connections to the database.
A variable in MySQL is closely related to the connection that has defined (born and dies with the connection, and if it was never defined its value will be NULL).
Each time you create a new database connection in PHP you have to define and give the correct value to the variable.
This means:

...
$user_id = $_SESSION['user_id'];
...
$link = mysql_connect($server, $username, $password);
mysql_query("SET @user_id = $user_id", $link);
mysql_query("DELETE FROM book WHERE id = $book_id", $link);
...
mysql_close($link);
...

See http://php.net/manual/en/function.mysql-connect.php
See http://php.net/manual/en/function.mysql-query.php

This way:
a) you can trust the variable in your trigger because you defined and gave it the correct value.
b) Yes but it depends also Yes, but it also depends on the php code you've written.
c) It makes no difference because each user will use a different database connection.

Collectives™ on Stack Overflow

Functioning of MySQL Session variables in trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related