MySQL Trigger causing unknown system variable error on existing row

Question

I am creating a trigger in MySQL and am getting an 'unknown system variable' on 'unique_id' which is a row in my 'pending_jobs' table. The trigger code is below:

CREATE TRIGGER format_pending_jobs_unique
BEFORE INSERT ON pending_jobs
FOR EACH ROW
BEGIN
   SET unique_id = CONCAT(prefix_unique_id, id);
END

Basically it's concatenating the two rows (prefix_unique_id and id) into the row unique_id when a new row is inserted. The prefix_unique_id row has a default value of "sa" which will be the value of all of them and the id is the index of the row with auto increment. I'm new to triggers and I had read in a post on Stack Overflow to use := instead of = but that didn't fix the problem. I'm using phpMyAdmin and see that the unique_id (as well as the prefix_unique_id and id) rows all exists. Any help would be great. Thanks!

fthiella · Accepted Answer · 2013-05-12 22:18:24Z

3

I would write your trigger this way:

CREATE TRIGGER format_pending_jobs_unique
BEFORE INSERT ON pending_jobs
FOR EACH ROW
  SET new.unique_id =
   CONCAT(
     new.prefix_unique_id,
     (SELECT AUTO_INCREMENT
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA=DATABASE()
            AND TABLE_NAME='pending_jobs')
   );

Please see fiddle here.

NEW refers to the column of a new row to be inserted, so you have to use new.unique_id and new.prefix_unique_id. To refer to the new value of the ID, you can't use new.id but you need to use a subquery that returns the next auto_increment value for the table.

answered May 12, 2013 at 22:18

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

3 Comments

MillerMedia Over a year ago

Seems to work perfect on the fiddle. I am getting an error when I try to add it in phpMyAdmin: "Access denied; you need the SUPER privilege for this operation"...I looked around for an answer but don't seem to have the option to update the privileges. I do have full access to the backend on this site so I may be logging in incorrectly?

fthiella Over a year ago

@MxmastaMills if you try to run just this query SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='pending_jobs' does it return something, or you get an error?

MillerMedia Over a year ago

No I don't get an error. It successfully runs the query. Should I be getting an error if I don't have SUPER privilege?

Collectives™ on Stack Overflow

MySQL Trigger causing unknown system variable error on existing row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related