11

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?

The trigger.

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)
gbn
  • 422,506
  • 82
  • 585
  • 676
Androme
  • 2,399
  • 4
  • 43
  • 82
  • You may have to disable the trigger in a test environment to verify that is indeed the problem. is it possible to remove the trigger-altered fields from the LINQ2SQL statement so it doesn't try to verify? – n8wrl May 06 '11 at 13:30
  • What type of trigger. Before update or after update? – Sam Saffron May 06 '11 at 13:30
  • could you add what the trigger looks like please? – DForck42 May 11 '11 at 15:36
  • Yes we have tried removing the trigger, and then everything works perfectly! The trigger runs after the update and just updated the "LastUpdated" colum to the current date, on the row that triggered the trigger. – Androme May 12 '11 at 14:23
  • From my previous reading, I gathered that triggers in Linq-SQL are a no-go. I could be wrong, but all I got was trouble from using them. – Hux May 12 '11 at 19:59
  • Can you describe the validation you have in place? How exactly is it failing. – arcain May 13 '11 at 06:07

4 Answers4

8

You probably need SET NOCOUNT ON in your trigger

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

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 ...
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
2

Are your triggers returning any data through SELECT statements? See this MSDN article: CREATE TRIGGER

When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

Also if you believe that the triggers are causing a heavy load on the database engine, have you considered using Service Broker to make their post-processing asynchronous?

If you could include the code for a representative trigger it would help us further diagnose the problem you're running into.

Yuck
  • 49,664
  • 13
  • 105
  • 135
  • The trigger has no select, and dose not return any date. I have added the code for the trigger in question in my first post! – Androme May 12 '11 at 14:24
0

You can take the help from SQL profiler for all the activities happening during the process.You can capture and save data about each event to a file or table.

Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • We have tryed using a SQL profiler, and all the commands that linq-to-sql sends to the database are working without problems. It is just linq-to-sql itself that thinks that it failed – Androme May 12 '11 at 14:25
0

If having the trigger update the property value behind your code's back is messing with your custom entity validation logic, can you either avoid the trigger altogether and directly set the LastUpdated property on the entity, or not perform any validation (other than schema validation) on the value of the LastUpdated property?

arcain
  • 14,920
  • 6
  • 55
  • 75