sql - RAISERROR() 的语法含义是什么

Question

我刚刚创建了一个 Instead After Trigger，其语法如下:

Create trigger tgrInsteadTrigger on copytableto
Instead of Insert as 
    Declare @store_name varchar(30);
    declare @sales int;
    declare @date datetime;

    select @store_name = i.store_name from inserted i
    select @sales = i.sales from inserted i
    select @date = i.Date from inserted i
begin
    if (@sales > 1000)
        begin
        RAISERROR('Cannot Insert where salary > 1000',16,1); ROLLBACK;
        end
    else
        begin
        insert into copytablefrom(store_name, sales, date) values (@store_name, @sales, @date);
        Print 'Instead After Trigger Executed';
        end
End

在上面的语法中，我使用了 RAISERROR('Cannot Insert where salary > 1000',16,1)

但是当我写 RAISERROR('Cannot Insert where salary > 1000') 时，它在同一行给出错误“Incorrect syntax near ')'”。

谁能在这里解释一下 (16,1) 的用法。

Answer 1

这是错误的严重级别。级别为 11 - 20，这会在 SQL 中引发错误。级别越高，级别越严重，交易应该被中止。

这样做会导致语法错误:

RAISERROR('Cannot Insert where salary > 1000').

因为您没有指定正确的参数(严重级别或状态)。

如果您希望发出警告而不是异常，请使用级别 0 - 10。

来自 MSDN:

severity

Is the user-defined severity level associated with this message. Whenusing msg_id to raise a user-defined message created usingsp_addmessage, the severity specified on RAISERROR overrides theseverity specified in sp_addmessage. Severity levels from 0 through 18can be specified by any user. Severity levels from 19 through 25 canonly be specified by members of the sysadmin fixed server role orusers with ALTER TRACE permissions. For severity levels from 19through 25, the WITH LOG option is required.

state

Is an integer from 0 through 255. Negative values or valueslarger than 255 generate an error. If the same user-defined error israised at multiple locations, using a unique state number for eachlocation can help find which section of code is raising the errors.For detailed description here