sql - 此 Sql Server 查询中的 CTE 语法有什么问题？

Question

谁能解释为什么 Sql Server 提示围绕“WITH”子句的语法？

感谢您的帮助。

CREATE TABLE TestTable1 (
    Id int not null,
    Version int not null constraint d_Ver default (0),
    [Name] nvarchar(50) not null,

    CONSTRAINT pk_TestTable1 PRIMARY KEY (Id, Version)
);
GO


CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    IF(
        ( 
            WITH MaxVers AS
                (SELECT Id, Max(Version) AS MaxVersion 
                FROM [dbo].[TestTable1]
                GROUP BY Id)
            SELECT Count(1) 
            FROM [dbo].[TestTable1] t
                INNER JOIN MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
            WHERE t.[Name] = inserted.[Name]
        )
        > 0
    )
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use.', 16, 1, @name);
    END
END;
GO

编辑 2:对于任何好奇的人，这里是包含以下所有重要评论的 CTE 版本。我想我会切换到子查询方法，以便我可以按照建议使用“EXISTS”。

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    DECLARE @cnt [int];
    WITH MaxVers AS
        (SELECT Id, Max(Version) AS MaxVersion 
        FROM [dbo].[TestTable1]
        GROUP BY Id)
        SELECT @cnt = COUNT(1) 
        FROM [dbo].[TestTable1] t
            INNER JOIN MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
            INNER JOIN [inserted] i ON t.[Id] = MaxVers.[Id]
        WHERE t.[Name] = i.[Name] AND NOT [t].[Id] = [i].[Id] ;
    IF( @cnt > 0)
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use by an active entity.', 16, 1, @name);

        ROLLBACK TRANSACTION;
    END
END;
GO 

编辑 3:这是“存在”版本(注意，我认为错误处理部分中的选择在插入多个记录时无法正常工作):

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN

    IF(EXISTS (
        SELECT t.Id 
        FROM [dbo].[TestTable1] t
            INNER JOIN (
                SELECT Id, Max(Version) AS MaxVersion 
                FROM [dbo].[TestTable1]
                GROUP BY Id) maxVer
                ON t.[Id] = [maxVer].[Id] AND [t].[Version] = [maxVer].[MaxVersion]
            INNER JOIN [inserted] i ON t.[Id] = MaxVer.[Id]
            WHERE [t].[Name] = [i].[Name] AND NOT [t].[Id] = [i].[Id]
        ))
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use by an active entity.', 16, 1, @name);

        ROLLBACK TRANSACTION;
    END
END;
GO

Answer 1

我唯一能想到的是语句“When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.”(Transact SQL 引用)意味着不能在 IF 语句中使用 CTE。

顺便说一句，您还有另外两个错误:1) inserted 伪表未包含在第一个子查询中，即使您在 were 子句中引用了它。 2)您的触发器假设正在插入或更新单行。可能会有多个重复名称，但 raiserror 只会报告其中一个。

编辑 并避免 (select count(*) ...) > 当 存在 (select * ....) 会做存在可以停在第一行。

EDIT 2 废话。 SQL Server 触发器默认为后触发器。因此，当触发器触发时，您正在检查是否存在的行已经存在于表中:

CREATE TRIGGER trg_iu_UniqueActiveName
ON [dbo].[TestTable1]
AFTER INSERT, UPDATE
AS
IF(UPDATE([Name]))
BEGIN
    IF EXISTS 
        ( 
            SELECT *
            FROM [dbo].[TestTable1] t
                INNER JOIN inserted i on i.[NAME] = t.[NAME]
                INNER JOIN (SELECT Id, Max(Version) AS MaxVersion 
                    FROM [dbo].[TestTable1]
                    GROUP BY Id) MaxVers ON t.Id = MaxVers.Id AND t.Version = MaxVers.MaxVersion
        )
    BEGIN
        DECLARE @name nvarchar(50)
        SELECT @name = [Name] FROM inserted;
        RAISERROR('The name "%s" is already in use.', 16, 1, @name);
    END
END;
GO

insert into testTable1 (name) values ('Hello')

结果:

Msg 50000, Level 16, State 1, Procedure trg_iu_UniqueActiveName, Line 20
The name "Hello" is already in use.

(1 row(s) affected)

另外，raiserror 不执行回滚，所以该行仍然存在。