mysql - 在触发器内使用变量

Question

我正在测试我的触发器，它运行良好，只是我看不出我的变量似乎没有像应有的那样工作。

例如

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id)  select cs_id,@the_last_insert_id from class_students where cs_class_id = 1 AND cs_year_id = 1;

END //
DELIMITER ;

在这一行

insert into examination_data (ed_cs_id,ed_examination_id)  select cs_id,the_last_insert_id from class_students where cs_class_id = 1 AND cs_year_id = 1;

the_last_insert_id 被视为一列

当我尝试这个时，@the_last_insert_id，它不被视为一列，但它也不起作用。我还没有尝试其余的变量。

例如，我将如何定义和使用这个 the_last_inserted_id = LAST_INSERT_ID();？

Answer 1

可以设置

Select STH into the_last_inserted_id from TBL limit 1;

insert into examination_data (ed_cs_id,ed_examination_id)
select cs_id,the_last_insert_id 
from class_students
where cs_class_id = 1 AND cs_year_id = 1;

编辑:@ - 您使用时无需声明，只需设置

Select STH into @new_id from TBL limit 1;

然后

insert into examination_data (ed_cs_id,ed_examination_id)
select cs_id,@the_last_insert_id
from class_students
where cs_class_id = 1 AND cs_year_id = 1;