Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using triggers for achieve this. Now at pdone db I have a table targets and at veevan I have a table address_vod__c. Every time a new record is inserted on address_vod__c or every time a record is updated I need to update the row at pdone.targets so this is how the process should run:

• INSERT a new record on veevan.address_vod__c, then SELECT the current inserted row (the values) find the match on pdone.targets and UPDATE columns

• UPDATE a record on veevan.address_vod__c, then SELECT the current updated row (the values) find the match on pdone.targets and UPDATE columns

This is what I have so far:

``
USE veevan;

DELIMITER $$

DROP TRIGGER IF EXISTS veevan.account_ai$$
USE veevan$$
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>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
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()
FROM (
SELECT
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_

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>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!

StackExchange Database Administrators Q#106820, answer score: 3