当附加了触发器时，LINQ-TO-SQL在插入和更新时失败 [英] Linq-to-sql failing on insert and update when this is a trigger attached

问题描述

我最近在linq-to-sql上遇到了一些问题.问题是当我们将触发器附加到事件上时，它认为"插入和更新失败. 一个示例可能是一行，当行被更改时，其中附加了触发器以将"LastUpdated"冒号设置为当前时间.这将导致linq-to-sql认为更新或插入失败，但这只是某些时候，因为有时它会通过，我认为这是sql服务器负载过重而无法执行此操作的时候在验证之前执行触发器，这仅仅是猜测. 由于我的脚本只是更大脚本的一部分，因此禁用触发器不是一种选择，因此我需要找到解决方案或重写程序. 你们有没有遇到过这个问题并找到了解决方案，例如在插入后禁用验证?

I am having some problems with linq-to-sql lately. The problem is that it "thinks" that it fails on inserts and updates when we have a trigger attached to the event. An example could be a row, where a trigger is attached to set the "LastUpdated" colon to the current time, when a row is altered. This will cause linq-to-sql to think that is have failed on the update or insert, but this is only some times, as it sometimes go through, I think this is when the sql server is under heavy load and where therefore not able to execute the trigger before the validation was made, this is only speculation. As my scripts are only a part of a much bigger script, so disabling the trigger is not an option, so I need to find a solution to this, or rewrite my program. Have any of you experienced this problem and have found a solution, for example disabling validation after inserts?

触发器.

USE [cnhha]
GO
/****** Object:  Trigger [dbo].[LastUpdated]    Script Date: 05/12/2011 16:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[LastUpdated] ON [dbo].[CN_User] 
FOR INSERT, UPDATE
AS

update cn_user set lastupdated=getdate() where campusnetuserid in (select campusnetuserid from inserted)

推荐答案

您可能需要

You probably need SET NOCOUNT ON in your trigger

除了我的问题使用时设置NOCOUNT"中提到的狭义情况(SQLDataAdapter) ，这是大多数客户端代码所必需的

Except the narrow case (SQLDataAdapter) mentioned in my question "SET NOCOUNT ON usage", it's required for most client code

如果您能够将客户端UPDATE更改为使用DEFAULT关键字，则也可以删除触发器

You can remove the trigger too if you are able to change your client side UPDATE to use the DEFAULT keyword

update cn_user
set col1 = this, col2 = that,...,
    lastupdated= DEFAULT
where ...

这篇关于当附加了触发器时，LINQ-TO-SQL在插入和更新时失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！