Trigger

如何使用 SELECT 中的值將行值更新到觸發器中的另一個數據庫中

  • July 13, 2015

我一直在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在 中的欄位標籤上使用了 a pdone.LCAPITAL(first)，這是不可能的。
  • 您function現在嘗試使用相同的CONCAT2 個欄位標籤，而不是表欄位名稱pdone.LCAPITAL(CONCAT(first,' ',last))。

現在，試試這個更新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 ;

希望這可以幫助。

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

相關問答

Mariadb

MariaDB：SELECT 查詢可以阻止其他查詢嗎？

  • July 7, 2022
檢視問答
Mysql

INSERT ON DUPLICATE KEY UPDATE 以在單個查詢中更新多行

  • February 8, 2020
檢視問答
Sql-Server

將觸發器插入連結伺服器錯誤

  • July 1, 2019
檢視問答
Mysql

如果使用部分，MySQL 使用觸發器更新另一個表上的數量

  • April 16, 2019
檢視問答
Mysql

更新觸發器插入循環後如何執行 MYSQL？

  • February 11, 2018
檢視問答
Sql-Server-2008

如何判斷是插入還是更新

  • January 8, 2018
檢視問答