在 MariaDB 觸發器中使用從外部數據庫和表中選擇更新列值

我有兩個數據庫pdone和veevan. pdone是一種大師，並veevan繼續與外部來源保持同步。我需要在兩者之間執行內部同步veevan，pdone所以我使用觸發器來實現這一點。現在在pdonedb 我有一張桌子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 中實現 UPDATE？每當我嘗試執行該 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 TRIGGERwith veevan.address_vod__c_ai。veevan.account_ai
• 我使用了一個JOIN與你相同的欄位SELECT，(address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)在UPDATE。

扳機：

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 ;

試試吧！

引用自：https://dba.stackexchange.com/questions/106820