Update columns values using select from external database and table in MariaDB trigger
Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw:

Errors:


You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$.
Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger.


Modifications:


I edited the DROP TRIGGER with veevan.address_vod__c_ai instead veevan.account_ai.
I used a JOIN with the same fields of your SELECT, with (address_vod__c.account_vod__c = NEW.account_vod__c AND  targets.veeva_account_id=NEW.account_vod__c) in the UPDATE.


TRIGGER:

USE `veevan`;
DELIMITER $$
DROP TRIGGER IF EXISTS veevan.address_vod__c_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount&gt;0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1); 

        UPDATE pdone.targets
        JOIN veevan.address_vod__c AS avc ON (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        WHERE address_vod__c.account_vod__c = NEW.account_vod__c
            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 veevan.recordtype WHERE name = 'Professional_vod');

    END IF;
END$$
DELIMITER ;


Try it!