1

I two DBs veeva and pdone as shown below in the pics

veeva DB pdone DB

As you can see veeva is simple and hasn't no constraints which relies on duplicate and wrong values. The problem happen exactly on userterritory where a user is assigned to one or more territory. In the new DB pdone that is not allowed so I need to find - from veeva - those reps that has only one territory and I got that through this query:

    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

After each INSERT on userterritory I execute a trigger that update reps table by setting up the right territories_id based on relationship from userterritory. This is the code for the trigger:

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

All is working perfectly, but I am getting reps with territories_id nulled and that is not allowed since each reps has to be assigned to one territories. See this pic:

enter image description here

So, how do I apply my query to the trigger in order to get right results? Can any give me some help?

1

I edited your TRIGGER to do this:

  1. I added the vTerritoryCount variable.

  2. If you have more than 1 rows with the same userid it wont update the reps.territories_id.

Here's the 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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.