50 lines
1.4 KiB
SQL
50 lines
1.4 KiB
SQL
CREATE TABLE `FinancialTransactionForeignIds` (
|
|
`FinancialTransactionForeignIdId` INT NOT NULL AUTO_INCREMENT,
|
|
`FinancialTransactionId` INT NOT NULL,
|
|
`ForeignIdType` INT NOT NULL,
|
|
`ForeignId` VARCHAR(256) NOT NULL,
|
|
PRIMARY KEY (`FinancialTransactionForeignIdId`),
|
|
INDEX `Ix_TxId` (`FinancialTransactionId` ASC),
|
|
INDEX `Ix_IdType` (`ForeignIdType` ASC),
|
|
INDEX `Ix_Identifier` (`ForeignId` ASC)
|
|
)
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `SetFinancialTransactionForeignIdentity`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetFinancialTransactionForeignIdentity`(
|
|
IN financialTransactionId INT,
|
|
IN foreignIdType INT,
|
|
IN foreignId VARCHAR(256)
|
|
)
|
|
BEGIN
|
|
|
|
IF ((SELECT COUNT(*) FROM FinancialTransactionForeignIds
|
|
WHERE FinancialTransactionForeignIds.FinancialTransactionId=financialTransactionId
|
|
AND FinancialTransactionForeignIds.ForeignIdType=foreignIdType) = 0)
|
|
THEN
|
|
|
|
INSERT INTO FinancialTransactionForeignIds (FinancialTransactionId,ForeignIdType,ForeignId)
|
|
VALUES (financialTransactionId,foreignIdType,foreignId);
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
ELSE
|
|
|
|
UPDATE FinancialTransactionForeignIds
|
|
SET FinancialTransactionForeignId.ForeignId=foreignId
|
|
WHERE FinancialTransactionForeignIds.FinancialTransactionId=financialTransactionId
|
|
AND FinancialTransactionForeignIds.ForeignIdType=foreignIdType;
|
|
|
|
SELECT 0 AS Identity;
|
|
|
|
END IF;
|
|
|
|
END
|