英文:
sql trigger unable to set variable from select
问题
我知道这是一个被问及的问题,然而我尝试了一些修改,但没有解决方案。
我的触发器:
BEGIN
DECLARE bookid INT;
DECLARE roomtype varchar(20);
DECLARE amount INT;
DECLARE m_count INT;
DECLARE curr_m varchar(20);
SET @bookid := NEW.id;
SET roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
SET amount := (SELECT amount from pm_booking_payment WHERE id_booking=@bookid);
SET @curr_m:= (MONTHNAME(NOW()));
SET @m_count:= (SELECT count(*) FROM pm_report WHERE month=@curr_m);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(@curr_m,@roomtype,@amount);
END
当我检查表格时,它插入了,但只有@curr,也就是月份的名字。其他的都插入了NULL。
我尝试过:
SET @roomtype SELECT title FROM pm_booking_room WHERE id_booking=@bookid
但仍然是相同的,NULL。
也尝试过:
SET @roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
但仍然是NULL。
我正在使用PhpMyadmin创建:
请帮忙。
英文:
I know its asked question ,however I have tried few amendments but to no solution .<br>
My trigger:
BEGIN
DECLARE bookid INT;
DECLARE roomtype varchar(20);
DECLARE amount INT;
DECLARE m_count INT;
DECLARE curr_m varchar(20);
SET @bookid := NEW.id;
SET roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
SET amount := (SELECT amount from pm_booking_payment WHERE id_booking=@bookid);
SET @curr_m:= (MONTHNAME(NOW()));
SET @m_count:= (SELECT count(*) FROM pm_report WHERE month=@curr_m);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(@curr_m,@roomtype,@amount);
END
whwn I check table, it inserts, but only @curr which is month name. Rest it inserts NULL
I tried
SET @roomtype SELECT title FROM pm_booking_room WHERE id_booking=@bookid
but still same,NULL .
Also tried SET @roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=@bookid);
but still NULL.<br>
I am using PhpMyadmin to create :
答案1
得分: 1
请注意,roomtype
和 @roomtype
是两个不同的变量。
您使用 local variable DECLARE statement 声明的变量在一个存储过程的主体内具有作用域。它们从不带有 @
标记。
带有 @
标记的 用户定义变量 具有 MySQL 会话的作用域。您不需要声明这些类型的变量。只需将变量设置为一个值即可隐式创建变量。
您不能使用 SET roomtype = ...
并期望从 @roomtype
变量中读取该字符串。反之亦然。
您似乎声明了局部变量,因此您应该一致使用它们。但在某些情况下,您的变量名称与列名相同,如果您在引用包含这些列的表的 SQL 语句中使用变量,这将导致歧义。因此,您应采用一种命名约定来使它们保持不同。
BEGIN
DECLARE v_bookid INT;
DECLARE v_roomtype varchar(20);
DECLARE v_amount INT;
DECLARE v_count INT;
DECLARE v_month varchar(20);
SET v_bookid := NEW.id;
SET v_roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=v_bookid);
SET v_amount := (SELECT amount from pm_booking_payment WHERE id_booking=v_bookid);
SET v_month:= (MONTHNAME(NOW()));
SET v_count:= (SELECT count(*) FROM pm_report WHERE month=v_month);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(v_month,v_roomtype,v_amount);
END
(此代码未经测试,如果有任何错误,敬请原谅。它仅用于演示如何一致使用非标记变量。)
英文:
Please be aware that roomtype
and @roomtype
are two different variables.
The variables you declare with the local variable DECLARE statement have a scope within the body of one stored routine. They are never spelled with a @
sigil.
The user-defined variables with the @
sigil have a scope of a MySQL session. You don't need to declare these kinds of variables. Just setting the variable to a value implicitly creates the variable.
You cannot SET roomtype = ...
and expect that string to be read from the @roomtype
variable. Nor vice-versa.
You appear to declare local variables, so you should use them consistently. But in some cases, your variable names are the same as column names, which will result in ambiguity if you use the variables in SQL statements that also reference tables with those columns. So you should adopt a naming convention to keep them distinct.
BEGIN
DECLARE v_bookid INT;
DECLARE v_roomtype varchar(20);
DECLARE v_amount INT;
DECLARE v_count INT;
DECLARE v_month varchar(20);
SET v_bookid := NEW.id;
SET v_roomtype := (SELECT title FROM pm_booking_room WHERE id_booking=v_bookid);
SET v_amount := (SELECT amount from pm_booking_payment WHERE id_booking=v_bookid);
SET v_month:= (MONTHNAME(NOW()));
SET v_count:= (SELECT count(*) FROM pm_report WHERE month=v_month);
INSERT INTO `pm_report`(`month`, `room_type`, `amount`) VALUES(v_month,v_roomtype,v_amount);
END
(This code is not tested, so apologies if there are any mistakes. It is meant only to demonstrate using non-sigil variables consistently.)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论