MySQL trigger take text variable

Question

Let's say we got two tables.

First table is items - id, title.
Second table is history - id, title, action, user.

We can have following AFTER INSERT trigger for "This user inserted this item":

INSERT INTO history (title, action, user) VALUES (NEW.title, 'INSERT', @phpUserId);

If I want to insert new item, I can do something like this.

SET @phpUserId = 123;
INSERT INTO items (title) VALUES ('My best item');

In this case, trigger works perfectly.

But the problem is, when I add some text into variable - for example SET @phpUserId = "library123"; - In this moment the trigger is not able to take that variable.

Any ideas why only integer variables are passed?

Please add your trigger to the question together with table definitions. — P.Salmon
– P.Salmon, Commented Jul 16, 2018 at 14:23

P.Salmon · Accepted Answer · 2018-07-16 15:00:55Z

1

Good news there's nothing wrong with your trigger and here's the proof

drop table if exists i,h;
create table i(id int, title varchar(20));
create table h(id int, title varchar(20), action varchar(20), user varchar(30));

drop trigger if exists t;
delimiter $$
create trigger t after insert on i
for each row 
begin
    INSERT INTO h (title, action, user) VALUES (NEW.title, 'INSERT', @phpUserId);
end $$

delimiter ;

SET @phpUserId = 123;
INSERT INTO i (title) VALUES ('My best item');
SET @phpUserId = 'bob123';
INSERT INTO i (title) VALUES ('My worst item');

+------+---------------+--------+--------+
| id   | title         | action | user   |
+------+---------------+--------+--------+
| NULL | My best item  | INSERT | 123    |
| NULL | My worst item | INSERT | bob123 |
+------+---------------+--------+--------+
2 rows in set (0.00 sec)

answered Jul 16, 2018 at 15:00

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL trigger take text variable

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