3

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__c AS `phone`,
            address_vod__c.fax_vod__c AS `fax`
            FROM
                address_vod__c avc
            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')
        )
        WHERE 
            targets.veeva_account_id=NEW.account_vod__c;
    END IF;
END$$
DELIMITER ;

But I don't know if the UPDATE using that SELECT will works and that is my doubt, how do I achieve an UPDATE from SELECT? Any time I try to run that SQL on I got this error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM SELECT address_vod__c.name AS address1, addr' at line 19

Can any give me some feedback and|or help on this?

veeva_account_id is the PK on pdone.targets and account_vod__c is the FK (kind of since that DB has not real relationships) on veevan.address_vod__c.

3

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!

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.