Eu tenho trabalhado em um gatilho para INSERT|UPDATE
valores para um determinado registro com base no mesmo PK
. O que o gatilho basicamente deve fazer é, foreach row
quando algum INSERT
acontece em veeva_new.account
:
- Verifique se o PK já existe, se existir, obtenha os valores
veeva_new.account
e asUPDATE
colunas empdone.targets
- Se PK não existir, então
INSERT
a nova linha (copie a linha deveeva_new.account
parapdone.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.account
e obter a linha atual com base em PK
porque ela PK
já existe e, em seguida, devo passar valores SELECT
para o UPDATE
declaração antes # [Will update all values for targets]
do comentário, como posso conseguir isso?
Eu modifiquei o seu
TRIGGER
e usei todosON DUPLICATE KEY UPDATE
osUPDATE
campos quando vocêNEW.lastmodifieddate
for maior quetargets.lastSyncAt
.Alguns erros :
function
em um rótulo de campo empdone.LCAPITAL(first)
, isso não pode ser possível.function
agora tentandoCONCAT
2 rótulos de campo, em vez de nomes de campos de tabelapdone.LCAPITAL(CONCAT(first,' ',last))
.Agora, tente esta atualização do
TRIGGER
:Espero que isto ajude.