问题描述

我刚刚创建了一个相反的触发器，其语法如下:

I just created a Instead After Trigger whose syntax is given below:

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 sale > 1000',16,1)

In the above syntax I have used RAISERROR('Cannot Insert where salary > 1000',16,1)

但是当我写 RAISERROR('Cannot Insert where sale > 1000') 时，它会在同一行给出错误Incorrect syntax near ')'".

But when I write RAISERROR('Cannot Insert where salary > 1000') it gives the error "Incorrect syntax near ')'" on the same line.

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

Can anyone please explain the use of (16,1) here.

推荐答案

是error的严重级别.级别是从 11 到 20，这会在 SQL 中引发错误.级别越高，级别越严重，transaction 应该被中止.

It is the severity level of the error. The levels are from 11 - 20 which throw an error in SQL. The higher the level, the more severe the level and the transaction should be aborted.

这样做时会出现语法错误:

You will get the syntax error when you do:

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

因为你没有指定正确的parameters(severity level或state).

Because you have not specified the correct parameters (severity level or state).

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

If you wish to issue a warning and not an exception, use levels 0 - 10.

来自 MSDN:

严重性

是与此消息关联的用户定义的严重级别.什么时候使用 msg_id 引发使用创建的用户定义消息sp_addmessage，在 RAISERROR 上指定的严重性会覆盖sp_addmessage 中指定的严重性.严重级别从 0 到 18可以由任何用户指定.从 19 到 25 的严重级别可以只能由 sysadmin 固定服务器角色的成员指定，或具有 ALTER TRACE 权限的用户.对于从 19 起的严重级别到 25，需要 WITH LOG 选项.

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

状态

是从 0 到 255 的整数.负值或值大于 255 会产生错误.如果相同的用户定义错误是在多个地点提出，每个地点使用唯一的州编号location 可以帮助找到哪个代码段引发了错误.详细说明这里

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

这篇关于RAISERROR() 的语法含义是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！