我有两个数据库pdone
和veevan
. pdone
是一种大师,并veevan
保持与外部源的持续同步。我需要在 和 之间执行内部同步veevan
,pdone
因此我使用触发器来实现此目的。现在在pdone
db 我有一张桌子targets
,在veevan
我有一张桌子address_vod__c
。每次插入新记录address_vod__c
或每次更新记录时,我都需要更新该行,pdone.targets
因此该过程应该如何运行:
INSERT
上的新记录veevan.address_vod__c
,然后SELECT
当前插入的行(值)在pdone.targets
和UPDATE
列上找到匹配项UPDATE
上的记录veevan.address_vod__c
,然后SELECT
当前更新的行(值)在pdone.targets
和UPDATE
列上找到匹配项
这是我到目前为止所拥有的:
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 ;
但我不知道UPDATE
使用SELECT
是否有效,这是我的疑问,我如何从 SELECT 实现更新?每当我尝试运行该 SQL 时,我都会收到此错误:
错误代码:1064。您的 SQL 语法有误;查看与您的 MariaDB 服务器版本对应的手册,了解
address1
在第 19 行的“FROM SELECT address_vod__c.name AS , addr”附近使用的正确语法
任何人都可以给我一些反馈和/或帮助吗?
veeva_account_id
是 PKpdone.targets
并且account_vod__c
是 FK(有点因为 DB 没有真正的关系)veevan.address_vod__c
。
你的
UPDATE
说法是错误的。我已经编辑了你的TRIGGER
和我所做的修改以及我看到的错误:错误:
TRIGGER
你要创建的不同的东西。DROP TRIGGER IF EXISTS veevan.account_ai$$
.@TargetLastMod
?你完全没有使用它trigger
。修改:
DROP TRIGGER
withveevan.address_vod__c_ai
insteadveevan.account_ai
。JOIN
与您相同的字段SELECT
,(address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
在UPDATE
.扳机:
试试看!