我需要根据我在这里得到的答案使用和过滤结果来更新表中的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_new
数据库上插入只是停止而没有错误,我只是在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;
我的触发器出了什么问题?
尝试:
假设
veeva_rep_id
=NEW.userid
(来自userterritory
)。这将在更新
reps.territories_id
时NEW.territoryid
更新,reps.lastSyncAt
当reps.lastSyncAt
大于@TerritoryLastMod
和时reps.updatedAt = now()
,当前时间戳。