不正确的 TSQL DISABLE 和 ENABLE 语句
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Lending].[uspHMDAUpdateExport] (@BatchId int, @ModifiedById int)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DISABLE TRIGGER Lending.utrHMDAAudit ON Lending.HMDA
UPDATE Lending.HMDA SET IsExported = 1,ModifiedById = @ModifiedById WHERE BatchId = @BatchId
ENABLE TRIGGER Lending.utrHMDAAudit ON Lending.HMDA
END
SET QUOTED_IDENTIFIER OFF
GO
GRANT EXECUTE ON [Lending].[uspHMDALarInfoGet] TO [caApplication] AS [dbo]
GO
说 DISABLE 和 ENABLE 语法错误。为什么?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Lending].[uspHMDAUpdateExport] (@BatchId int, @ModifiedById int)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DISABLE TRIGGER Lending.utrHMDAAudit ON Lending.HMDA
UPDATE Lending.HMDA SET IsExported = 1,ModifiedById = @ModifiedById WHERE BatchId = @BatchId
ENABLE TRIGGER Lending.utrHMDAAudit ON Lending.HMDA
END
SET QUOTED_IDENTIFIER OFF
GO
GRANT EXECUTE ON [Lending].[uspHMDALarInfoGet] TO [caApplication] AS [dbo]
GO
Says the DISABLE and ENABLE syntax is wrong. Why?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要一些分号(在
DISABLE
之前的语句末尾以及ENABLE
之前的语句末尾)附注将隔离设置为
READ UNCOMMITTED
和禁用触发器对我来说似乎都非常可疑。在这种情况下,隔离级别似乎不会对以下语句产生任何影响,那么这是您所有过程中的标准做法吗?如果是这样,您是否意识到它的潜在问题?
关于禁用触发器,什么是在禁用触发器时阻止另一个并发事务修改表?
You need some semicolons (at the end of the statement preceding
DISABLE
and at the end of the statement beforeENABLE
)On a side note setting the isolation to
READ UNCOMMITTED
and disabling triggers both seem distinctly dubious to me.In this case the isolation level doesn't seem like it would make any difference to the following statements so is this standard practice in all your procedures? If so are you aware of the potential issues with it?
Regarding the trigger disabling what is to stop another concurrent transaction modifying the table whilst the trigger is disabled?
应该是
,但是您的触发器真的名为 Lending.utrHMDAAudit 吗?
另外
您知道如果您的更新在过程中失败,触发器将被禁用,对吧?您的过程没有任何错误处理,违反约束将中止批处理并且您的触发器被禁用
最好在过程调用之后和之前禁用和启用触发器
It should be
but is your trigger really named Lending.utrHMDAAudit?
Also
You know that if your update fails in the proc that the trigger will be disabled right? Your proc doesn't have any error handling, a constraint violation will abort the batch and your trigger is disabled
Better to disable and enable the trigger after the proc call and before