使用触发器在mysql中计算列值？

u0sqgete 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(209)

我有一张table listlocations 有列的 name , stateId , countryId ，和 displayName . stateId 指列出美国/领土及其缩写的表格，以及 countryId 指国家及其缩写的表格，以及 displayName 我要显示的字符串是 name 以及州/国家缩写。
实现 displayName ，我有一个存储过程，在我的 locations 下表-

CREATE DEFINER=`root`@`%` PROCEDURE `updateLocationDisplayName`(in locationId int, in stateId int, in countryId int)
BEGIN
    IF (stateId = -1 AND countryId = -1) OR (countryId = 220 and stateId = -1)
        THEN
        UPDATE listlocations
        SET displayName = name
        WHERE idx = locationId;
    ELSEIF (countryId = 220 and stateId !=-1)
        THEN
        UPDATE listlocations ll
        LEFT JOIN listunitedstates us ON us.idx = ll.stateIdx
        SET displayName = CONCAT(ll.name, ', ', us.abbreviation)
        WHERE idx = locationId;
    ELSEIF (countryId != -1 AND countryId != 220)
        THEN
        UPDATE listlocations ll
        LEFT JOIN listcountries lc ON lc.idx = ll.countryIdx
        SET displayName = CONCAT(ll.name, ', ', lc.abbreviation)
        WHERE idx = locationId;
    END IF;
END

但现在，试图更新列表位置，我得到 Error Code: 1442. Can't update table 'listlocations' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 在MySQL5.6中，处理这种情况的“正确”方法是什么？在MySQL5.6中，您有一个基于其他列计算的列，并且希望它在插入/更新时保持更新（并且在db本身中处理，而不是在应用程序代码中处理）？

sql mysql mysql-5.6

来源：https://stackoverflow.com/questions/63176009/calculated-column-value-in-mysql-using-a-trigger

2条答案

按热度按时间

wh6knrhe1#

您可以在“new”语句中使用“before update”触发器。

赞(0）回复(0）举报 2021-07-27

llew8vvj2#

我的错误是试图使用 UPDATE 触发器中的语句，而我真正需要做的就是使用 SET . 做到以下几点就是成功的原因

DELIMITER $$
CREATE TRIGGER `listlocation_AINS` BEFORE INSERT ON `listlocations`
FOR EACH ROW
BEGIN
    IF (NEW.countryIdx = -1 AND NEW.stateIdx = -1) OR (NEW.countryIdx = 220 and NEW.stateIdx = -1)
        THEN
            SET NEW.displayName = NEW.name;
    ELSEIF NEW.countryIdx = 220 AND NEW.stateIdx != -1
        THEN
            SET @stateAbbr = (SELECT abbreviation FROM listunitedstates WHERE idx = NEW.stateIdx);
            SET NEW.displayName = CONCAT(NEW.name, ', ', @stateAbbr);
    ELSEIF NEW.countryIdx != 220 AND NEW.countryIdx != -1
        THEN
            SET @countryAbbr = (SELECT abbreviation FROM listcountries WHERE idx = NEW.countryIdx);
            SET NEW.displayName = CONCAT(NEW.name, ', ', @countryAbbr);
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `listlocation_AUPD` BEFORE UPDATE ON `listlocations`
FOR EACH ROW
BEGIN
    IF (NEW.countryIdx = -1 AND NEW.stateIdx = -1) OR (NEW.countryIdx = 220 and NEW.stateIdx = -1)
        THEN
            SET NEW.displayName = NEW.name;
    ELSEIF NEW.countryIdx = 220 AND NEW.stateIdx != -1
        THEN
            SET @stateAbbr = (SELECT abbreviation FROM listunitedstates WHERE idx = NEW.stateIdx);
            SET NEW.displayName = CONCAT(NEW.name, ', ', @stateAbbr);
    ELSEIF NEW.countryIdx != 220 AND NEW.countryIdx != -1
        THEN
            SET @countryAbbr = (SELECT abbreviation FROM listcountries WHERE idx = NEW.countryIdx);
            SET NEW.displayName = CONCAT(NEW.name, ', ', @countryAbbr);
    END IF;
END$$
DELIMITER ;

赞(0）回复(0）举报 2021-07-27

我来回答

使用触发器在mysql中计算列值？

2条答案

相关问题

热门标签

最新问答