locked
Problem using an insert trigger with ADO.NET Data Services RRS feed

  • Question

  • I am testing out a web service created with ADO.NET Data Services (Astoria). Version is Dec 2007 CTP under Windows XP.

    The web service is generated following the documentation for “Creating a local data service using Visual Studio” in the document “Using ADO.NET Data Services (“Project Astoria”). The database is very simple and effectively consists of just one main table.

     

    Everything has worked pretty well and looks good. However, I have hit one problem that has defeated me:

     

    I have an insert trigger on the main table and have discovered that updates do not complete if the trigger contains a select statement that returns zero rows.

     

    e.g.

     

    on the database:

     

    ALTER TRIGGER [dbo].[TR_InsertToSubsidiaryDB]

      ON [dbo].[LegacyRecords]

      AFTER INSERT

    AS

    BEGIN

      CREATE TABLE #temp(id INT)

      INSERT INTO #temp(id) SELECT 1

      EXEC('SELECT id FROM #temp WHERE 1=0')

    END

     

    and on the client:

     

    [TestMethod]

    public void TestAdd()

    {

      LegacyRecord lr = new LegacyRecord;

      ctx = new LegacyEntities(baseUri);

      ctx.MergeOption = MergeOption.OverwriteChanges;

      ctx.AddObject("LegacyRecords", lr);

      ctx.SaveChanges();

      Assert.AreNotEqual(0, lr.id);

    }

     

    The assertion fails and I get the following debug output:

     

    System.Net.WebException: The remote server returned an error: (500) Internal Server Error.

       at System.Net.HttpWebRequest.GetResponse()

       at Microsoft.Data.WebClient.WebDataContext.SaveChanges()

       at LegacyRawClient.LegacyRaw_RestClient.SaveChanges() in C:\Visual Studio 2008\Projects\LegacyRaw RestClient\WCF_RestClient.cs:line 172

    Store update, insert or delete statement affected unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh given ObjectStateManager entries.

       at System.Data.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int32 rowsAffected, UpdateCommand source)

     

    Change 1=0 to 1=1 and run again and the update completes. In both cases, all of the trigger code runs and does not error.

    If I run a similar test in T-SQL (i.e. with no Web Service involved), the update completes in both cases.

     

    It looks to me as if an error occurs in the Astoria framework code that runs after the trigger is invoked but I don't know how to go about debugging that...

     

     

    Wednesday, February 27, 2008 5:29 PM

Answers

  • The EntityFramework checks the row count being returned from the DML statement being excuted on the database server.  In this case, by returining a rowset from the trigger, it changes the rowcount being returned.  Hence, when you have the predicate of 1=0, the rowcount is 0 and hence the concurrency error is raised.  When the predicate is 1=1, the rowcount is 1 and the Entity Framework thinks the value is persisted correctly.

     

    I will forward this issue on to an Entity Framework Program manager to see if we can get some guidance on what kind of operations are supported in triggers when using the Entity Framework.

     

    Wednesday, February 27, 2008 9:40 PM
    Moderator
  • Andy is correct – EF does check the number of rows affected to infer whether the updated succeeded or not. You can easily set a non-negative ROWCOUNT using something like this:

    declare @x int;

    set @x = 1;

     

    The more interesting case is when you have to perform multiple operations, but something fails midway. Then you’ll have to raise an error.

     

    Anyway, a cleaner approach is to use stored procedures for modifications, not triggers. In that case the rowcount is communicated as an explicit output parameter.

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Wednesday, February 27, 2008 11:51 PM

All replies

  • The EntityFramework checks the row count being returned from the DML statement being excuted on the database server.  In this case, by returining a rowset from the trigger, it changes the rowcount being returned.  Hence, when you have the predicate of 1=0, the rowcount is 0 and hence the concurrency error is raised.  When the predicate is 1=1, the rowcount is 1 and the Entity Framework thinks the value is persisted correctly.

     

    I will forward this issue on to an Entity Framework Program manager to see if we can get some guidance on what kind of operations are supported in triggers when using the Entity Framework.

     

    Wednesday, February 27, 2008 9:40 PM
    Moderator
  • Andy is correct – EF does check the number of rows affected to infer whether the updated succeeded or not. You can easily set a non-negative ROWCOUNT using something like this:

    declare @x int;

    set @x = 1;

     

    The more interesting case is when you have to perform multiple operations, but something fails midway. Then you’ll have to raise an error.

     

    Anyway, a cleaner approach is to use stored procedures for modifications, not triggers. In that case the rowcount is communicated as an explicit output parameter.

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Wednesday, February 27, 2008 11:51 PM
  • Thank you to Andrew and Zlatko.

     

    I now understand a little bit more and will look at restructuring the code. One further thing that I have discovered is that, if you have several SELECTs, it's the result of the first one that seems to determine the "result" of the trigger.

     

    Interestingly, although the @x=1 does indeed set the ROWCOUNT, it has no effect on the Web Service update, I still have to force a SELECT statement to ensure that the update completes.

     

    Friday, February 29, 2008 12:16 PM