时间戳

首页 > 解决方案 > 如何使用内部联接创建触发器

mysql - 如何使用内部联接创建触发器

问题描述

当贷款表中有插入时，我正在尝试创建一个触发器来填充 aud 贷款表。我希望这个 aud 表有来自 Loan 表和另一个表的数据，所以我试图设置获取这些数据的变量。

创建触发器时出现错误“未知系统变量'var1'”

这是数据库布局：

https://cdn.discordapp.com/attachments/582912082450710528/583696750322253824/unknown.png

DELIMITER $$
CREATE TRIGGER Loan_Insert AFTER INSERT ON loan
FOR EACH ROW
BEGIN

SET var1 =
(SELECT loan_type.type_of_loan
FROM loan INNER JOIN loan_type ON
loan.loan_type_idloan_type = loan_type.idloan_type
AND
loan.loan_type_idapp_type = loan_type.idapp_type
WHERE loan.loan_type_idloan_type = new.loan_type_idloan_type
AND loan.loan_type_idapp_type = new.loan_type_idapp_type);

SET var2 =
(SELECT loan_type.app_type
FROM loan INNER JOIN loan_type ON
loan.loan_type_idloan_type = loan_type.idloan_type
AND
loan.loan_type_idapp_type = loan_type.idapp_type
WHERE loan.loan_type_idloan_type = new.loan_type_idloan_type
AND loan.loan_type_idapp_type = new.loan_type_idapp_type);


INSERT INTO Aud_Loan(bk_Loan, type_of_loan, type_of_loan_description, application_type,
application_type_description, insert_date)
VALUES(new.idloan, new.loan_type_idloan_type, var1, new.loan_type_idapp_type, var2,CURDATE());
END $$

标签： mysqlviewtriggersinner-join

解决方案

  • 局部变量必须是DECLAREd。文档
  • 使用 SET 时，右侧只能返回一个值（单个结果和单个字段）。

在您的特定情况下，您可能可以像这样调整 SET 语句中使用的查询：

SET var1 = (
   SELECT loan_type.type_of_loan
   FROM loan_type 
   WHERE loan_type.idloan_type = NEW.loan_type_idloan_type
      AND loan_type.idapp_type = NEW.loan_type_idapp_type
);

您甚至可以使用SELECT INTO将其减少为一个查询：

SELECT loan_type.type_of_loan, loan_type.app_type
INTO var1, var2
FROM loan_type 
WHERE loan_type.idloan_type = NEW.loan_type_idloan_type
   AND loan_type.idapp_type = NEW.loan_type_idapp_type
;

推荐阅读