Is it possbile to have a SQL trigger for CRUD queries that gets an extra parameter from the query it-CodePudding

Triggers are new to me and I'm just wondering how can I fully benefit on using them.

Here are the two tables shelfs and shelfs_log:

CREATE TABLE `shelfs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shelf_name` VARCHAR(50) NULL DEFAULT NULL
    `storage_id` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
)

CREATE TABLE `shelfs_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shelf_name` VARCHAR(50) NULL DEFAULT NULL
    `storage_id` INT(11) NOT NULL,
    `user_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
)

PHP function that gets executed when a new shelf is created to a storage: $shelf_name, $storage_id (and $user_id):

public static function createNewShelf($shelf_name, $storage_id)
    {
        global $conn;
        $sql = "INSERT INTO shelfs (shelf_name, storage_id)
        VALUES ('$shelf_name', '$storage_id')";

        $result = $conn->query($sql);

        $newShelf = array();

        if ($result) {
            $newShelf['success'] = true;
        } else {
            $newShelf['success'] = false;
        }

        return $newShelf;

        $conn->close();
    }

But I would like to have a trigger that keeps log of these inserts to a separete table but also having the user_id as an extra parameter that I could pass on these INSERT queries without writing the user_id to shelfs table.

Trigger would be something like:

CREATE DEFINER=`root`@`localhost` TRIGGER `shelfs` 
AFTER INSERT ON `shelfs` 
FOR EACH ROW 
    INSERT INTO shelfs_log (shelf_name, storage_id, user_id) 
    VALUES (NEW.shelf_name, NEW.storage_id, NEW.user_id)

A little hard me to explain and I couldn't find help for this matter on google.

Thanks in advance!

CodePudding user response：

Please, show me how I can pass the user_id without writing it to the first table if its possible. – Manny

Extra data not passed into the query can be passed via user-defined variable:

CREATE DEFINER=`root`@`localhost` TRIGGER `shelfs` 
AFTER INSERT ON `shelfs`
FOR EACH ROW 
  INSERT INTO shelfs_log (shelf_name, storage_id, user_id) 
  VALUES (NEW.shelf_name, NEW.storage_id, @user_id);

Method 1. Assign the value before INSERT query.

-- set the variable
SET @user_id := '444';
-- now insert
INSERT INTO shelfs (shelf_name, storage_id)
VALUES ('name 1', '11'),  ('name 2', '22');

Both queries must be executed in the same connection. Multiple rows with the same user_id can be inserted.

Method 2. Assign the value in the query using INSERT .. SELECT.

INSERT INTO shelfs (shelf_name, storage_id)
SELECT 'name 3', '33'
FROM ( SELECT @user_id := 555) set_variable
UNION ALL
SELECT 'name 4', '44';

Multiple rows can be inserted using UNION ALL in SELECT. But all of them will use the same user_id value taken from the most last subquery (i.e. only one subquery should contain FROM clause, all other may not have it).

Method 3. Use inline assignment.

INSERT INTO shelfs (shelf_name, storage_id)
VALUES ('name 5', CASE WHEN (@user_id := 666) IS NOT NULL THEN '55' END), 
       ('name 6', CASE WHEN (@user_id := 777) IS NOT NULL THEN '66' END);

Multiple rows with different user_id values can be inserted.

DEMO fiddle