0
create table kit(id int unsigned not null auto_increment, creator int unsigned not null, name varchar(16) not null, script longtext not null, tag as concat(select username from user where id = creator, '/', name), primary key (id));

doesn't work because I am trying to make tag a computed column

I want two tables, user looks like

+----+------------------+
| id | username         |
+----+------------------+
|  1 | 1234567890123456 |
+----+------------------+

and kit looks like

+----+---------+--------+--------+-------------------------+
| id | creator |  name  | script |           tag           |
+----+---------+--------+--------+-------------------------+
|  1 |       1 | kitkit | long   | 1234567890123456/kitkit |
+----+---------+--------+--------+-------------------------+

The tag column should be auto-computed from the username of the creator and the name of the kit.

I thought my column declaration would work:

tag as concat(select username from user where id = creator, '/', name)

but I guess not.

theonlygusti
  • 11,032
  • 11
  • 64
  • 119

1 Answers1

0

Something like this (NB! not tested)

create table kit(
id int unsigned not null auto_increment, 
creator int unsigned not null, 
name varchar(16) not null, 
script longtext not null, 
tag varchar(33) not null, primary key (id));

DELIMITER //
CREATE TRIGGER contacts_before_insert
BEFORE INSERT
   ON contacts FOR EACH ROW
BEGIN
   DECLARE vtag varchar(33);
   -- Fill tag value
   select concat(username,'/',OLD.name) from user where id = OLD.creator INTO vtag;

   -- Update created_by field to the username of the person performing the INSERT
   SET NEW.tag = vtag;

END; //

DELIMITER ;
Vasyl Moskalov
  • 4,242
  • 3
  • 20
  • 28
  • what's delimiter – theonlygusti Jan 29 '19 at 03:49
  • From [https://stackoverflow.com/questions/10259504] > Note that the DELIMITER keyword is a function of the command line mysql client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. – Vasyl Moskalov Jan 29 '19 at 05:28