在 userterritory 上插入後，在觸發器中使用正確的值更新 reps 表

我有兩個數據庫veeva，pdone如下圖所示

如您所見veeva，它很簡單，並且沒有依賴於重複值和錯誤值的約束。問題恰好發生在userterritory將 auser分配給一個或多個的位置territory。pdone在不允許的新數據庫中，所以我需要從veeva那些只有一個的代表中找到territory，我通過這個查詢得到了它：

   SELECT
       count(*) AS reps,
       userid,
       lastmodifieddate,
       territoryid
   FROM
       (
           SELECT
               userid,
               territoryid,
               count(*) AS territories
           FROM
               userterritory
           GROUP BY
               userid
           HAVING
               territories = 1
       ) T1
   INNER JOIN (
       SELECT
           id AS userid,
           lastmodifieddate
       FROM
           `user`
       WHERE
           `user`.`id` IN (
               SELECT
                   userterritory.`userid`
               FROM
                   userterritory
           )
   ) T2 USING (userid)
   GROUP BY
       territoryid
   HAVING
       reps = 1

每次之後INSERT，我通過根據來自的關係設置權限來userterritory執行更新表的觸發器。這是觸發器的程式碼：reps``territories_id``userterritory

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
   # [ VARIABLES DECLARATION ]
   DECLARE vUserID VARCHAR(18) DEFAULT '';
   DECLARE vTerritoriRep VARCHAR(45) DEFAULT '';
   DECLARE vTerrytoriLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
   # [ VARIABLES ASIGNATION ]
   SET vUserID         =NEW.userid;
   SET vTerritoriRep   =(select IFNULL(reps.territories_id,'N/A') from pdone.reps where reps.veeva_rep_id=vUserID LIMIT 0,1);
   SET vTerrytoriLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);

IF (NEW.territoryid != vTerritoriRep) THEN
   UPDATE pdone.reps 
   SET reps.territories_id = NEW.territoryid, 
       reps.lastSyncAt = IF(NOW()>vTerrytoriLastMod,NOW(),reps.lastSyncAt), 
       reps.updatedAt=now() 
   WHERE reps.veeva_rep_id=vUserID; 
END IF;
END

一切工作正常，但我得到reps了territories_idnulled 並且這是不允許的，因為每個reps都必須分配給一個territories。看這張照片：

那麼，如何將查詢應用於觸發器以獲得正確的結果？任何人都可以給我一些幫助嗎？

我編輯你TRIGGER這樣做：

1. 我添加了vTerritoryCount變數。
2. 如果您有超過 1 行相同userid，則不會更新reps.territories_id.

這是TRIGGER：

USE `veeva_new`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
   # [ VARIABLES DECLARATION ]
   DECLARE vUserID VARCHAR(18) DEFAULT '';
   DECLARE vTerritoryRep VARCHAR(45) DEFAULT '';
   DECLARE vTerritoryLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
   DECLARE vTerritoryCount INT(7) DEFAULT 0; #[060715.01]
   # [ VARIABLES ASIGNATION ]
   SET vUserID         =NEW.userid;
   SET vTerritoryRep   =(SELECT IFNULL(reps.territories_id,'N/A') FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
   SET vTerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
   SET vTerritoryCount =(SELECT COUNT(*) FROM veeva_new.userterritory WHERE userterritory.userid=vUserID);
IF (NEW.territoryid != vTerritoryRep) THEN # [ IF THE NEW TERRITORYID IS DIFFERENT FROM TERRIROTYID IN reps TABLE ]
   IF (vTerritoryCount>1) THEN
       SET @Blah='There\'s more than 1 rows, I\'m not going to do anything.';
   ELSE
       UPDATE pdone.reps 
       SET reps.territories_id = NEW.territoryid, 
           reps.lastSyncAt = IF(NOW()>vTerritoryLastMod,NOW(),reps.lastSyncAt), 
           reps.updatedAt=now() 
       WHERE reps.veeva_rep_id=vUserID;
   END IF;
END IF;
END

引用自：https://dba.stackexchange.com/questions/106071