在插入和更新时将ISBN10转换为ISBN13

Question

我试图用触发器将ISBN10转换为ISBN13。我的代码出了问题，它不起作用(下面是错误代码)。另外，如果您对在ISBN13后面的值是'X‘有任何想法，可以随意提供建议。

扳机

CREATE TRIGGER ISBN
ON Library.Book
FOR INSERT, UPDATE
AS
IF (SELECT COUNT(*) FROM INSERTED) > 0
BEGIN
    DECLARE @isbn VARCHAR(13),
    @i INT = 1,
    @x INT = 0,
    @isbn13 varchar(13),
    @id INT,
    @name VARCHAR(200)

    DECLARE Upda CURSOR
    FOR SELECT Id,Isbn,Name
        FROM inserted

    OPEN Upda

    FETCH NEXT
    FROM Upda
    INTO @id,@isbn,@name


    WHILE(@@FETCH_STATUS = 0)
    BEGIN
        IF Len(@isbn) = 10
        BEGIN
            SET @isbn13 = '978' + LEFT(@isbn,9)
            WHILE @i <= 9
            BEGIN
            SELECT @x = @x + (SUBSTRING(@isbn,@i,1)* CASE WHEN @i % 2 = 1 THEN 1 ELSE 3 END)
            SELECT @i = @i + 1
            END

            SELECT @x = 10 - (@x % 10)
            
            SELECT @isbn13 = @isbn13 + CONVERT(CHAR(1), @x)
            INSERT INTO Library.Book(Id,Isbn,Name)
            VALUES(@id,@isbn13,@name)
        END
    END
END

使用该代码，如果我试图在表中插入新值，就会得到一个错误“违反主键约束。无法在对象‘PRIMARY . code’中插入重复键。重复的键值是(4)行Msg 2627，14级，状态1，过程ISBN，第39行

INSERT INTO Library.Book (Id, Isbn, Name)
VALUES (1, '9512913151', 'Book 1'),
       (2, NULL, 'Book 2'),
       (3, '9781119245513', 'Book 3'),
       (4, '951884111X','Book 4')

因此，目标是Id 4将成为ISBN13，当我更新Id 2值ISBN时，它应该自动转换为ISBN13。

UPDATE Library.Book
SET Isbn = '0321189566'
WHERE Id = 2

Answer 1

好的，您的TRIGGER有几个问题。正如我在评论中所说的，多次，它不是基于设置的，这是一个很大的问题。当您处理几行时，这可以很容易地将您的数据库带到爬行；因为您在ISBN中的行和每个字符上循环。对于仅仅100行，您有1,200个迭代！那需要修理。

让我们先创建一些示例数据：

CREATE TABLE dbo.Book (ID int PRIMARY KEY,
                       ISBN char(13),
                       [Name] varchar(50));
GO
INSERT INTO dbo.Book (Id, Isbn, Name)
VALUES (1, '9512913151', 'Book 1'),
       (2, NULL, 'Book 2'),
       (3, '9781119245513', 'Book 3'),
       (4, '951884111X','Book 4');

现在我做了一个谷歌，找出算法是什么，将一个ISBN10转换为一个ISBN13，并找到了这个文章。

对于您拥有的数据，这使我看到了基于集的解决方案：

WITH ISBN AS(
    SELECT ID,
           '978' + LEFT(B.ISBN,9) AS ISBN12
    FROM dbo.Book B
    WHERE LEN(B.ISBN) = 10)
SELECT I.ID,
       I.ISBN12 + RIGHT(10-SUM(CASE V.I % 2 WHEN 0 THEN TRY_CONVERT(int,SS.C) * 3 ELSE TRY_CONVERT(int,SS.C) END)%10,1) AS ISDBN13
FROM ISBN I
     CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))V(I)
     CROSS APPLY (VALUES(SUBSTRING(I.ISBN12,V.I,1)))SS(C)
GROUP BY I.ID, I.ISBN12;

现在，问题是'X'。根据另一个谷歌的说法，'X'的意思是10，所以我们可以更新上面的内容来处理这个问题：

WITH ISBN AS(
    SELECT ID,
           '978' + LEFT(B.ISBN,9) AS ISBN12
    FROM dbo.Book B
    WHERE LEN(B.ISBN) = 10)
SELECT I.ID,
       I.ISBN12 + RIGHT(10-SUM(CASE V.I % 2 WHEN 0 THEN N.I * 3 ELSE N.I END)%10,1) AS ISDBN13
FROM ISBN I
     CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))V(I)
     CROSS APPLY (VALUES(SUBSTRING(I.ISBN12,V.I,1)))SS(C)
     CROSS APPLY (VALUES(CASE SS.C WHEN 'X' THEN 10 ELSE TRY_CONVERT(int,SS.C) END)) AS N(I)
GROUP BY I.ID, I.ISBN12;

最后，我们可以把它放进TRIGGER.

CREATE TRIGGER Book_ISDB10_to_ISBN13 ON dbo.Book
FOR INSERT,UPDATE
AS

    WITH ISBN AS(
        SELECT i.ID,
               '978' + LEFT(i.ISBN,9) AS ISBN12
        FROM inserted i
        WHERE LEN(i.ISBN) = 10),
    ISDN13 AS(
        SELECT I.ID,
               I.ISBN12 + RIGHT(10-SUM(CASE V.I % 2 WHEN 0 THEN N.I * 3 ELSE N.I END)%10,1) AS ISDN13
        FROM ISBN I 
             CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))V(I)
             CROSS APPLY (VALUES(SUBSTRING(I.ISBN12,V.I,1)))SS(C)
             CROSS APPLY (VALUES(CASE SS.C WHEN 'X' THEN 10 ELSE TRY_CONVERT(int,SS.C) END)) AS N(I)
        GROUP BY I.ID, I.ISBN12)
    UPDATE B
    SET ISBN = I.ISDN13
    FROM dbo.Book B
         JOIN ISDN13 I ON B.ID = I.ID;

GO

注意，我只更新需要更新的行，而不是所有行。

然后我们可以对表进行TRUNCATE，并检查数据：

TRUNCATE TABLE dbo.Book;
GO
INSERT INTO dbo.Book (Id, Isbn, Name)
VALUES (1, '9512913151', 'Book 1'),
       (2, NULL, 'Book 2'),
       (3, '9781119245513', 'Book 3'),
       (4, '951884111X','Book 4');
GO
SELECT *
FROM dbo.Book;
GO

db<>fiddle

引用自isbn-information.com文章以供完成：

ISBN-10向ISBN-13的转换 下面是我们在下面七个步骤中要做的：

• 取10位数字ISBN (10位数字)
• 删除最后一个字符(9位数字)
• 将"978“按在前面(12位数字)
• 计算一个新的检查数字并将其插入到末尾(13位数字)

我们开始吧。

启动ISBN-10的示例

1861972717

1. 把ISBN-10的所有10位数都拿出来，然后删除最后一个数字--算了吧。最后一个数字是一个检查数字，它只适合10位ISBN --我们需要在下面的步骤3到6中为新的13位ISBN计算一个新的数字。(支票数字是怎么回事？( ISBN检查数字页的详细资料。) 186197271
2. 准备数字"978“到前面的九位数字。(为什么是978？见ISBN-13页。) 978186197271
3. 在这和接下来的三个阶段中，我们必须计算新的校验数字来附加到步骤2中的12位数字，我们从步骤2中取每一个数字，然后将它们乘以一个数字。我们把第一位数字乘以1，第二位数字乘以3，第三位数字再乘以1，第四位数字再乘以3，等等，把第一位数和第三位数相乘，直到第十二位数。 这是我得到的。你得到同样的东西了吗？ 9 x1=9 7x3=2 1 8x1=8 1 x3=3 8 x1=8 6x3= 18 1 x1=1 9 x3=2 7x1=7 2 x3=6 7 x1=7 1 x3=3
4. 现在，把你在第三步中得到的全部12个答案加起来。 9+ 21 +8+3+8+ 18 +1+ 27 +7+6+7+3= 118
5. 从步骤4中获得答案，并执行模块化10师。模10师也称为“抛出10s”。当你做一个整数除法时，它只是剩下的部分(与小数除法相反，在小数除法中，你期望得到一个小数部分答案)。 118 mod 10 =8.118除以10 = 11余数8，我们感兴趣的只是剩余数。 8
6. 计算ISBN-13的校验位的最后一步.接受步骤5的结果，如果为零，则检查数字为零。如果步骤5的结果不是零，那么从数字10中减去步骤5的结果。结果是校验数字。 10-8= 2。
7. 从ISBN-10转换我们的ISBN-13的最后一步是将步骤6中到达的检查数字附加到我们在步骤2中到达的12位数字的末尾。 9781861972712