1

I have been working in a trigger for INSERT|UPDATE values for a given record based on same PK. What trigger basically should do it is, foreach row when some INSERT happens on veeva_new.account:

  • Check if PK already exists, if exists then get the values from veeva_new.account and UPDATE columns on pdone.targets
  • If PK doesn't exists then INSERT the new row (copy row from veeva_new.account to pdone.targets)

This is what I have so far:

#Account ON INSERT AFTER => Add a new record to the targets table or UPDATE its values if record already exists (checking by PK)
USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.account_ai$$
USE `veeva_new`$$
CREATE DEFINER = CURRENT_USER TRIGGER `veeva_new`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;

    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.id);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.id LIMIT 0,1); 

        IF (NEW.lastmodifieddate > @TargetLastMod) THEN
            # [Will execute a query for get values from veeva]
            SELECT 
                account.id AS veeva_account_id,
                account.lastmodifieddate AS veeva_timestamp,
                pdone.LCAPITAL(CONCAT(`first`,' ',`last`)) as display_name,
                'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
                account.title__c AS `title`,
                account.firstname AS pdone.LCAPITAL(`first`),
                account.lastname AS pdone.LCAPITAL(`last`),
                account.suffix_vod__c AS `suffix`,
                address_vod__c.name AS `address1`,
                address_vod__c.address_line_2_vod__c AS `address2`,
                address_vod__c.city_vod__c AS `city`,
                address_vod__c.state_vod__c AS `state`,
                address_vod__c.zip_vod__c AS `zip`,
                address_vod__c.phone_vod__c AS `phone`,
                address_vod__c.fax_vod__c AS `fax`,
                -- `state_licensed_id`
                'VEEVA' AS `target_type`,
                account.npi_vod__c AS `npi`,
                FALSE AS `inactive`,
                NOW() AS `lastSyncAt`,
                NOW() AS `createdAt`,
                NOW() AS `updatedAt`
                -- address_vod__c.primary_vod__c,
                -- address_vod__c.license_vod__c,
                -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
            FROM
                account
                    JOIN
                address_vod__c ON address_vod__c.account_vod__c = account.id
            WHERE
                account.id = NEW.id
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod');

            # [Will update all values for targets]
            UPDATE pdone.targets 
            SET
            `veeva_account_id` =,
            `veeva_timestamp` =,
            `display_name` =,
            `avatar_url` =,
            `title` =,
            `first` =,
            `last` =,
            `suffix` =,
            `address1` =,
            `address2` =,
            `city` =,
            `state` =,
            `zip` =,
            `phone` =,
            `fax` =,
            `target_type` =,
            `npi` =,
            `inactive` =,
            `lastSyncAt` = NOW(),
            `updatedAt` = NOW()
            WHERE targets.veeva_account_id=NEW.id; 
        END IF;
    ELSE
        INSERT INTO pdone.targets
        (`veeva_account_id`,`veeva_timestamp`,`display_name`,`avatar_url`,`title`,`first`,`last`,`suffix`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`fax`,`target_type`,`npi`,`inactive`,`lastSyncAt`,`createdAt`,`updatedAt`)
        SELECT 
            account.id AS veeva_account_id,
            account.lastmodifieddate AS veeva_timestamp,
            pdone.LCAPITAL(CONCAT(`first`,' ',`last`)) as display_name,
            'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
            account.title__c AS `title`,
            account.firstname AS pdone.LCAPITAL(`first`),
            account.lastname AS pdone.LCAPITAL(`last`),
            account.suffix_vod__c AS `suffix`,
            address_vod__c.name AS `address1`,
            address_vod__c.address_line_2_vod__c AS `address2`,
            address_vod__c.city_vod__c AS `city`,
            address_vod__c.state_vod__c AS `state`,
            address_vod__c.zip_vod__c AS `zip`,
            address_vod__c.phone_vod__c AS `phone`,
            address_vod__c.fax_vod__c AS `fax`,
            -- `state_licensed_id`
            'VEEVA' AS `target_type`,
            account.npi_vod__c AS `npi`,
            FALSE AS `inactive`,
            NOW() AS `lastSyncAt`,
            NOW() AS `createdAt`,
            NOW() AS `updatedAt`
            -- address_vod__c.primary_vod__c,
            -- address_vod__c.license_vod__c,
            -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
        FROM
            account
                JOIN
            address_vod__c ON address_vod__c.account_vod__c = account.id
        WHERE
            account.id = NEW.id
                AND external_id_vod__c IS NOT NULL
                AND address_vod__c.primary_vod__c = 1
                        AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod');
    END IF;
END
$$
DELIMITER ;

Trigger won't work since it has a lot of issues and I am trying to fix them so, between lines # [Will execute a query for get values from veeva] and # [Will update all values for targets] I should perform a query to veeva_new.account and get current row based on PK because that PK already exists and then I should pass values on that SELECT to the UPDATE statement just before # [Will update all values for targets] comment, how I can achieve that?

1

I've modified your TRIGGER and I used ON DUPLICATE KEY UPDATE to UPDATE all the fields when your NEW.lastmodifieddate be greater than targets.lastSyncAt.

Some errors:

  • You've used a function on a field label in pdone.LCAPITAL(first), that can't be possible.
  • You've used the same function now trying to CONCAT 2 field labels, instead table fields name pdone.LCAPITAL(CONCAT(first,' ',last)).

Now, try this update of the TRIGGER:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.account_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.id);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.id LIMIT 0,1); 
        # [ IF EXISTS IN pdone.targets, UPDATE only if NEW.lastmodifieddate IS GREATER than @TargetLastMod ]
        IF (NEW.lastmodifieddate > @TargetLastMod) THEN
            INSERT INTO pdone.targets
            (`veeva_account_id`,`veeva_timestamp`,`display_name`,`avatar_url`,`title`,`first`,`last`,`suffix`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`fax`,`target_type`,`npi`,`inactive`,`lastSyncAt`,`createdAt`,`updatedAt`)
            SELECT 
                account.id AS veeva_account_id,
                account.lastmodifieddate AS veeva_timestamp,
                pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)) as display_name,
                'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
                account.title__c AS `title`,
                pdone.LCAPITAL(account.firstname) AS `first`,
                pdone.LCAPITAL(account.lastname) AS `last`,
                account.suffix_vod__c AS `suffix`,
                address_vod__c.name AS `address1`,
                address_vod__c.address_line_2_vod__c AS `address2`,
                address_vod__c.city_vod__c AS `city`,
                address_vod__c.state_vod__c AS `state`,
                address_vod__c.zip_vod__c AS `zip`,
                address_vod__c.phone_vod__c AS `phone`,
                address_vod__c.fax_vod__c AS `fax`,
                -- `state_licensed_id`
                'VEEVA' AS `target_type`,
                account.npi_vod__c AS `npi`,
                FALSE AS `inactive`,
                NOW() AS `lastSyncAt`,
                NOW() AS `createdAt`,
                NOW() AS `updatedAt`
                -- address_vod__c.primary_vod__c,
                -- address_vod__c.license_vod__c,
                -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
            FROM
                account
                    JOIN
                address_vod__c ON address_vod__c.account_vod__c = account.id
            WHERE
                account.id = NEW.id
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod')
            ON DUPLICATE KEY UPDATE
            # `veeva_account_id` =, THIS IS TE PK, COULDNT BE UPDATE
            `veeva_timestamp` =NEW.lastmodifieddate,
            `display_name` = pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)),
            `avatar_url` = 'https://pdone.s3.amazonaws.com/avatar/no_avatar.png',
            `title` =account.title__c,
            `first` =pdone.LCAPITAL(account.firstname),
            `last` =pdone.LCAPITAL(account.lastname),
            `suffix` =account.suffix_vod__c,
            `address1` =address_vod__c.name,
            `address2` =address_vod__c.address_line_2_vod__c,
            `city` =address_vod__c.city_vod__c,
            `state` =address_vod__c.state_vod__c,
            `zip` =address_vod__c.zip_vod__c,
            `phone` =address_vod__c.phone_vod__c,
            `fax` =address_vod__c.fax_vod__c,
            `target_type` ='VEEVA',
            `npi` =account.npi_vod__c,
            `inactive` =FALSE,
            `lastSyncAt` = NOW(),
            `updatedAt` = NOW();
        END IF;
    ELSE
        INSERT INTO pdone.targets
            (`veeva_account_id`,`veeva_timestamp`,`display_name`,`avatar_url`,`title`,`first`,`last`,`suffix`,`address1`,`address2`,`city`,`state`,`zip`,`phone`,`fax`,`target_type`,`npi`,`inactive`,`lastSyncAt`,`createdAt`,`updatedAt`)
            SELECT 
                account.id AS veeva_account_id,
                account.lastmodifieddate AS veeva_timestamp,
                pdone.LCAPITAL(CONCAT(account.firstname,' ',account.lastname)) as display_name,
                'https://pdone.s3.amazonaws.com/avatar/no_avatar.png' AS avatar_url,
                account.title__c AS `title`,
                pdone.LCAPITAL(account.firstname) AS `first`,
                pdone.LCAPITAL(account.lastname) AS `last`,
                account.suffix_vod__c AS `suffix`,
                address_vod__c.name AS `address1`,
                address_vod__c.address_line_2_vod__c AS `address2`,
                address_vod__c.city_vod__c AS `city`,
                address_vod__c.state_vod__c AS `state`,
                address_vod__c.zip_vod__c AS `zip`,
                address_vod__c.phone_vod__c AS `phone`,
                address_vod__c.fax_vod__c AS `fax`,
                -- `state_licensed_id`
                'VEEVA' AS `target_type`,
                account.npi_vod__c AS `npi`,
                FALSE AS `inactive`,
                NOW() AS `lastSyncAt`,
                NOW() AS `createdAt`,
                NOW() AS `updatedAt`
                -- address_vod__c.primary_vod__c,
                -- address_vod__c.license_vod__c,
                -- address_vod__c.lastmodifieddate AS addresslastmodifieddate
            FROM
                account
                    JOIN
                address_vod__c ON address_vod__c.account_vod__c = account.id
            WHERE
                account.id = NEW.id
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veeva_new.recordtype WHERE name = 'Professional_vod');
    END IF;
END$$
DELIMITER ;

Hope this helps.

  • That should works when trigger is ON AFTER INSERT but what about ON AFTER UPDATE? How I should deal with that trigger? – ReynierPM Jul 13 '15 at 17:51

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.