How to update row values with values from SELECT into another DB in a trigger

Question

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?

Answer 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.

• ReynierPM: That should works when trigger is ON AFTER INSERT but what about ON AFTER UPDATE? How I should deal with that trigger?