我有两个数据库veeva
,pdone
如下图所示
如您所见veeva
,它很简单并且没有依赖于重复值和错误值的约束。问题恰好发生在userterritory
将 auser
分配给 one 或 more的位置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_id
nulled 并且这是不允许的,因为每个reps
都必须分配给一个territories
. 看这张照片:
那么,如何将查询应用于触发器以获得正确的结果呢?可以给我一些帮助吗?
我编辑你
TRIGGER
这样做:我添加了
vTerritoryCount
变量。如果您有超过 1 行相同的行,
userid
它不会更新reps.territories_id
.这是
TRIGGER
: