觸發器失敗，完全沒有錯誤，為什麼？

我需要根據我在這裡得到的答案使用和過濾結果來更新表中的territory_id列，我正在嘗試建構自己的，這就是它的樣子：pdone.reps``veeva_new.userterritory

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
   CREATE DEFINER=`root`@`%` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
   BEGIN
   SET @TerritoryVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid);

   IF (@TerritoryVerification>0) THEN
       SET @TerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=NEW.userid LIMIT 0,1); 

       IF (NEW.lastmodifieddate > @TerritoryLastMod) THEN
           UPDATE pdone.reps 
           SET reps.territories_id = NEW.territoryid, reps.lastSyncAt = NEW.lastmodifieddate, reps.updatedAt=now() 
           WHERE reps.veeva_rep_id=NEW.userid; 
       END IF;
   END IF;
   END$$
DELIMITER ;

但它不起作用，因為在veeva_newDB 上的插入只是停止而沒有錯誤，我只是在general.log：

11 Query COMMIT
          11 Query SELECT sync_time FROM _sync_history WHERE object_name='userterritory' ORDER BY sync_time DESC LIMIT 1
150703 12:48:42    11 Query START TRANSACTION
          11 Query INSERT INTO _sync_history (object_name, sync_time) VALUES('userterritory', '2015-07-03 17:18:26')
          11 Query INSERT INTO userterritory (id,lastmodifieddate,userid,territoryid) VALUES('04S80000000uZi9EAE','2009-08-24 17:56:49','00580000001sYARAA2','04T800000008yHOEAY'),('04S80000000uoFIEAY','2009-09-24 19:20:02','00580000002Ka2yAAC','04T800000008yHOEAY')$
          11 Query SET @UserVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid)
          11 Query ROLLBACK
          11 Quit

這是veeva_new.userterritory表的架構：

CREATE TABLE `userterritory` (
 `id` varchar(18) NOT NULL,
 `userid` varchar(18) DEFAULT NULL,
 `territoryid` varchar(18) DEFAULT NULL,
 `lastmodifieddate` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

reps.territories_id有一個約束reps.territories，這是reps.territories表的架構：

CREATE TABLE `territories` (
 `veeva_territory_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 `description` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
 `district` int(11) DEFAULT NULL,
 `inactive` tinyint(1) NOT NULL,
 `createdAt` datetime NOT NULL,
 `updatedAt` datetime NOT NULL,
 PRIMARY KEY (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我的觸發器有什麼問題？

嘗試：

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
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$$
DELIMITER ;

假設veeva_rep_id= NEW.userid(從userterritory)。

這將在 更新reps.territories_id時NEW.territoryid更新，reps.lastSyncAt當reps.lastSyncAt大於@TerritoryLastMod和時reps.updatedAt = now()，目前時間戳。

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