Início / dba / Perguntas / 106783
Accepted
ReynierPM
Asked: 2015-07-14 07:26:56 +0800 CST

Como atualizar valores de linha com valores de SELECT em outro banco de dados em um gatilho

  • 772

Eu tenho trabalhado em um gatilho para INSERT|UPDATEvalores para um determinado registro com base no mesmo PK. O que o gatilho basicamente deve fazer é, foreach rowquando algum INSERTacontece em veeva_new.account:

  • Verifique se o PK já existe, se existir, obtenha os valores veeva_new.accounte as UPDATEcolunas empdone.targets
  • Se PK não existir, então INSERTa nova linha (copie a linha de veeva_new.accountpara pdone.targets)

Isto é o que eu tenho até agora:

#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 ;

O gatilho não funcionará, pois tem muitos problemas e estou tentando corrigi-los, então, entre as linhas # [Will execute a query for get values from veeva], # [Will update all values for targets]devo executar uma consulta veeva_new.accounte obter a linha atual com base em PKporque ela PKjá existe e, em seguida, devo passar valores SELECTpara o UPDATEdeclaração antes # [Will update all values for targets]do comentário, como posso conseguir isso?

update trigger

1 respostas

  1. Best Answer

    Eu modifiquei o seu TRIGGERe usei todos ON DUPLICATE KEY UPDATEos UPDATEcampos quando você NEW.lastmodifieddatefor maior que targets.lastSyncAt.

    Alguns erros :

    • Você usou um functionem um rótulo de campo em pdone.LCAPITAL(first), isso não pode ser possível.
    • Você usou o mesmo functionagora tentando CONCAT2 rótulos de campo, em vez de nomes de campos de tabela pdone.LCAPITAL(CONCAT(first,' ',last)).

    Agora, tente esta atualização do 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 ;

    Espero que isto ajude.

    • 1

relate perguntas