我一直在为INSERT|UPDATE
基于相同的给定记录的值触发器工作PK
。触发器基本上应该做的是,row
当某些INSERT
事情发生时foreach veeva_new.account
:
- 检查 PK 是否已经存在,如果存在则从
veeva_new.account
和UPDATE
列中获取值pdone.targets
- 如果 PK 不存在则
INSERT
新行(从复制行veeva_new.account
到pdone.targets
)
这是我到目前为止所拥有的:
#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 ;
触发器将无法工作,因为它有很多问题,我正在尝试修复它们,因此,在行与行之间# [Will execute a query for get values from veeva]
,# [Will update all values for targets]
我应该执行查询veeva_new.account
并获取当前行,PK
因为它PK
已经存在,然后我应该将其值传递SELECT
给评论UPDATE
之前的声明# [Will update all values for targets]
,我该如何实现?
我修改了你的,当你大于时
TRIGGER
,我习惯ON DUPLICATE KEY UPDATE
了UPDATE
所有的领域。NEW.lastmodifieddate
targets.lastSyncAt
一些错误:
function
在 中的字段标签上使用了pdone.LCAPITAL(first)
,这是不可能的。function
现在尝试使用相同的CONCAT
2 个字段标签,而不是表字段名称pdone.LCAPITAL(CONCAT(first,' ',last))
。现在,试试这个更新
TRIGGER
:希望这可以帮助。