无法从触发器内的SELECT查询中获取值 [英] Cannot fetch a value from a SELECT query inside a trigger

问题描述

我正在编写一个触发器，以在另一个表上插入后更新表中的行。这些是表的脚本：

I am writing a trigger to UPDATE a row in a table AFTER INSERT on another table. Here are the scripts of the tables:

表 inv_cost

CREATE TABLE inv_cost (
  Username varchar(20) NOT NULL DEFAULT '',
  MachineType varchar(2) NOT NULL,
  Cost smallint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (Username,MachineType),
)

表调查

CREATE TABLE investigation (
  Username varchar(20) NOT NULL,
  MachineType varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER ;;
CREATE TRIGGER TRG1 AFTER INSERT ON investigation FOR EACH ROW BEGIN
    DECLARE cost INT DEFAULT 0;
    SET cost = (SELECT Cost FROM inv_cost WHERE Username = NEW.Username AND MachineType = NEW.MachineType);
    UPDATE test SET Balance = Balance - cost WHERE Username = New.Username;
END;;
DELIMITER ;

表研究者

CREATE TABLE test (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  Username varchar(20) NOT NULL DEFAULT '',
  Balance smallint(6) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
DELIMITER ;;
CREATE TRIGGER TRG2 AFTER UPDATE ON test FOR EACH ROW BEGIN
    IF NEW.Balance > OLD.Balance THEN
        INSERT INTO payments SET Username = NEW.Username, PaymentOn = NOW(), Amount = NEW.Balance - OLD.Balance;
    END IF;
END;;
DELIMITER ;

触发器 TRG1 的问题在于它不计算SELECT语句中的变量 cost ，并且始终采用值0或在其声明中设置为DEFAULT的值。下一行的UPDATE查询工作良好（无论 cost 取自声明中的任何值，或者如果为它分配了 SET cost = 100 ;）。 SELECT运行单独给出 Cost 的必需值。

The problem with trigger TRG1 is that it does not compute the value of the variable cost from the SELECT statement, and always takes the value 0 or whatever is set to DEFAULT in its declaration. The UPDATE query on the next line works good (with whatever value cost takes from its declaration OR if it is assigned a const value like SET cost = 100;). The SELECT run separately gives the required value of Cost.

此代码有什么问题？

预先感谢。

推荐答案

您必须使用sintax SELECT字段中的WHERE条件INTO表变量

You have to use the sintax SELECT field FROM table WHERE condition INTO variable

DROP TRIGGER TRG1 IF EXISTS;
DELIMITER $$
 CREATE TRIGGER TRG1
 AFTER INSERT ON investigation
 FOR EACH ROW BEGIN
      DECLARE cst smallint;
      SELECT Cost FROM inv_cost WHERE Username = NEW.Username AND MachineType = NEW.MachineType INTO cst;
      UPDATE test SET Balance = Balance - cst WHERE Username = New.Username;
 END$$
 DELIMITER ;

这篇关于无法从触发器内的SELECT查询中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！