Question 289331 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I access the inserted record in a subquery inside of an SQL trigger?

I am creating a database that holds information for tests, test questions, and the questions on each test. I have 3 tables: problem, test, & question_list. problem holds the information for a question, test holds the information for a test, and question_list holds the keys for a test and all the problems it has.

I am trying to create a trigger that will insert a number of problems into question_list when a test is inserted based on the new test_id, problem_count, and category. Here is what I have:

DELIMITER #
CREATE TRIGGER generate_question_list
AFTER INSERT ON test
FOR EACH ROW
BEGIN
     INSERT INTO question_list (test_id, problem_id)
     SELECT NEW.test_id, p.problem_id
     FROM problem p 
     WHERE p.category REGEXP NEW.category
     ORDER BY RAND() 
     LIMIT NEW.problem_count;
END #

My problem is that it’s giving me an error and telling me that it doesn’t know what NEW is. I assume this is because I am trying to reference NEW in the SELECT statement. How can I get around this and make this work?

Tags:

2

Answers


  1. 0

    For taht you need dynamic sql, as MySQL expects there a number

    DELIMITER #
CREATE TRIGGER generate_question_list
AFTER INSERT ON test
FOR EACH ROW
BEGIN
    SET @d = NEW.problem_count;
    PREPARE stmt FROM'
     INSERT INTO question_list (test_id, problem_id)
     SELECT NEW.test_id, p.problem_id
     FROM problem p 
     WHERE p.category REGEXP NEW.category
     ORDER BY RAND() 
     LIMIT ? ;';
    EXECUTE stmt using @d;
    DEALLOCATE PREPARE stmt; 
END #
    Login or Signup to reply.
  2. 0

    The argument in the LIMIT clause does not support an expression.

    https://dev.mysql.com/doc/refman/8.0/en/select.html says:

    LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

    • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

    • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

    The simplest solution is to declare a local variable:

    ...
BEGIN
     DECLARE v_problem_count INT;
     SET v_problem_count = NEW.problem_count;

     INSERT INTO question_list (test_id, problem_id)
     SELECT NEW.test_id, p.problem_id
     FROM problem p 
     WHERE p.category REGEXP NEW.category
     ORDER BY RAND() 
     LIMIT v_problem_count;
END

    P.S.: We can’t use PREPARE/EXECUTE as the other answer suggests. In MySQL, we cannot use prepared statements within triggers or functions, because the caller may itself be a prepared statement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search