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.