我觉得是“if @@rowcount = 1”出了问题。会不会是Set NoCount Off之后@@rowcount的值为零(不会自动改变)造成的?<以下是从MSDN上摘抄的资料>SET NOCOUNT
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.Syntax
SET NOCOUNT { ON | OFF } Res
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.The setting of SET NOCOUNT is set at execute or run time and not at parse time.Permissions
SET NOCOUNT permissions default to all users.
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.Syntax
SET NOCOUNT { ON | OFF } Res
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.The setting of SET NOCOUNT is set at execute or run time and not at parse time.Permissions
SET NOCOUNT permissions default to all users.
delete tr_kehchp where (kehid=1001) and (jizh='A222')删除的数据是不是只有一条。
我想可能是你第一次执行delete tr_kehchp where (kehid=1001) and (jizh='A222')时,删除的数据不是一条,这样@@rowcount 就不等于1,后面的就不会执行,当你第二次删除时(当然你要先加一条kehid=1001 and jizh='A222'的数据在tr_kehchp 表中),就能删除(触發器中能被触發)
kehid和jizh在tr_kehchp表中是屬于主鍵值,當然是絕對唯一的了。
这样就可以了吧。
declare @rowcount int
select @rowcount = @@rowcount
set nocount off
if @rowcount = 0
declare @rowcount int
select @rowcount = @@rowcount
set nocount off
if @rowcount = 1
...