CODEWITHSUNDEEP
mysqlsqlmysql-5.6
bjk116
bjk116

Reputation: 529

Calculated column value in MySQL using a trigger?

I have a table listlocations that has columns name, stateId, countryId, and displayName. stateId refers to a table that lists the united states/territories with their abbreviations, and countryId refers to a table of countries and their abbreviations, and displayName is the string I want shown, a combination of name and the state/country abbreviations.

To achieve displayName, I have a stored procedure that runs after insert/after update on my locations table as follows -

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

But now, trying to update listlocations, I get 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.

What is the "right" way to have handle this sort of case in MySQL 5.6 - where you have a column that is calculated based on other columns, and you want it to stay updated on inserts/updates (and handled in the DB itself, not app code)?

Upvotes: 0

Views: 61

Answers (2)

bjk116
bjk116

Reputation: 529

My mistake was trying to use UPDATE statements in the trigger prior, when really all I needed to do was use SET. Doing the following is what made it work

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 ;

Upvotes: 0

Anton Anisimov
Anton Anisimov

Reputation: 66

You can use "before update" trigger with "new" statements.

Upvotes: 1

Related Questions