“Member AutoSync Failure” using TPT, view and instead of insert trigger

Question

Trying to use Linq2SQL with class inheritance using TPT pattern like this http://blogs.msdn.com/sbajaj/archive/2008/04/02/tpt-with-linq-to-sql.aspx

Sample application works fine. Here is my code:

Tables and views:

 

CREATE TABLE [dbo].[social_event](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Creator] [uniqueidentifier] NOT NULL FOREIGN KEY REFERENCES [dbo].[aspnet_Users](UserId),
    [EventDate] [datetime] NOT NULL)
GO

CREATE TABLE [dbo].[social_addfriendevent](
    [ID] [int] NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES [dbo].[social_event](Id),
    [Friend] [uniqueidentifier] NOT NULL FOREIGN KEY REFERENCES [dbo].[aspnet_Users](UserId),
    [Added] [bit] NOT NULL DEFAULT((0)))
GO

CREATE VIEW [dbo].[event]
AS
SELECT t0.Id, t0.Creator, t0.EventDate, t1.Friend, t1.Added, 'AddFriendEvent' AS EventType
FROM  dbo.social_event AS t0 INNER JOIN
   dbo.social_addfriendevent AS t1 ON t0.Id = t1.Id
GO

 

 My "Instead of insert" trigger:

 

CREATE TRIGGER [dbo].[trigger_update_event] ON [dbo].[event] INSTEAD OF INSERT
AS
    DECLARE @rc AS INT;
    SET @rc = @@ROWCOUNT;
    IF @rc = 0 RETURN;

    DECLARE @Id AS INT
    DECLARE @EventType AS NVARCHAR(50)
    DECLARE @Creator AS uniqueidentifier
    DECLARE @EventDate AS datetime
    DECLARE @Friend AS uniqueidentifier
    DECLARE @Added AS bit 

    IF @rc = 1
        BEGIN
            SELECT @Id=Id,
   @EventType=EventType,
                 @Creator=Creator,
                 @EventDate = getdate(),
                 @Friend=Friend,
                 @Added=Added
            FROM INSERTED

            INSERT INTO dbo.social_event VALUES(@Creator, @EventDate)
            SELECT @Id = SCOPE_IDENTITY()
            IF @EventType = 'AddFriendEvent'
                INSERT INTO dbo.social_addfriendevent VALUES(@Id, @Friend, @Added)                            
        END
    ELSE
        BEGIN
            DECLARE cursor_inserted CURSOR FAST_FORWARD FOR
                SELECT EventType,
                     Creator,
                     EventDate,
                     Friend,
                     Added
                FROM INSERTED
            OPEN cursor_inserted;

            FETCH NEXT FROM cursor_inserted INTO @EventType,
                                             @Creator,
                                             @EventDate,
                                             @Friend,
                                             @Added;
            WHILE @@FETCH_STATUS = 0
            BEGIN
                INSERT INTO dbo.social_event VALUES(@Creator, @EventDate)
                SELECT @Id = SCOPE_IDENTITY()                
                IF @EventType = 'AddFriendEvent'
                INSERT INTO dbo.social_addfriendevent VALUES(@Id, @Friend, @Added)
            END
            CLOSE cursor_inserted;
            DEALLOCATE cursor_inserted;
        END

 

My data classes with mappings:

 

[InheritanceMapping(Code = "AddFriendEvent", Type = typeof(AddFriendEvent), IsDefault = true)]
 [Table(Name = "dbo.event")]
 public abstract class Event
 {
 private static int s_id;

 public Event()
 {
  this.Id = --Event.s_id;
 }

 public Event(string eventType)
  : this()
 {
  EventType = eventType;
 }

 [Column(IsPrimaryKey = true)]
 public int Id;

 [Column(Name = "Creator")]
 public Guid CreatorId;

 [Column(Name = "EventDate", DbType = "datetime")] 
 public DateTime Date;

 [Column(IsDiscriminator = true, Name = "EventType")]
 public string EventType;
 }

 public class AddFriendEvent : Event
 {
 [Column(Name = "Added", CanBeNull = false)]
 public bool Added;

 [Column(Name = "Friend", CanBeNull = false)]
 public Guid Friend;

 public AddFriendEvent()
  : base("AddFriendEvent")
 {
 }
 }

 

When I try to create new "AddFriendEvent" and save it to database like here:

 

 

DataContext dc = new DataContext(connectionString);
   Table<Event> allEvents = dc.GetTable<Event>();
   AddFriendEvent ev = new AddFriendEvent()
   {
    Added = true,
    CreatorId = Guid.Parse("379959D-58FE-4A57-BB02-559E3E855B8F"),
    Date = DateTime.Now,
    Friend = Guid.Parse("379959D-58FE-4A57-BB02-559E3E855B8F")
   };
   allEvents.InsertOnSubmit(ev);
   dc.SubmitChanges();

 

I got "Member AutoSync Failure". What's wrong?

Answer 1

Currently my friend and me also had same problem. It is related to query generated by LinqtoSQL. When ever you are insert against view using trigger problem occur b'coz of scope_identity used by linq to sql.

Return @ID from trigger at last that you required. B'coz if do not do this it return NULL and that cause the problem.

Answer 2

Thanks, this helped me find the problem. Bascially don't use any selects to different tables within your trigger. Even if you do, just assign the values of the select to some variables declared in the trigger, that should fix the problem. E.g

        SELECT TOP 1
                @accountStart = def.AccountNoStart, 
                @accountEnd = def.AccountNoEnd,
                @barcode = I.Barcode
        FROM HSBarCodeDefinition def
        INNER JOIN INSERTED I ON def.BarcodeLength = LEN(I.Barcode)     

The ID gets messed up and this is what casues all problems.

Answer 3

I had a similar problem to this and I wanted to share my solution since it took me nearly six hours to debug.

Turns out that one of my AFTER INSERT SQL triggers was causing this System.InvalidOperationException : Member AutoSync exception but no error was occurring when I inserted the same record within SSMS.

Here is the piece of the trigger code that was causing problems:

DECLARE someCursor CURSOR FOR SELECT someIntValue FROM someTable
WHERE someColumn = 1

OPEN someCursor

WHILE (1 = 1)
BEGIN
    FETCH NEXT FROM someCursor

    IF (@@FETCH_STATUS <> 0)
    BREAK

    // For each row, insert into another table
    INSERT INTO AnotherTable (Column1, Column2) VALUES (3,4)
END
CLOSE someCursor
DEALLOCATE someCursor

The problem line was FETCH NEXT FROM someCursor - I wasn't using the INTO keyword, which per MSDN SQL documentation it isn't required.

So I changed my code to this and my AutoSync exception disappeared.

DECLARE @localVariable int

DECLARE someCursor CURSOR FOR SELECT someIntValue FROM someTable
WHERE someColumn = 1

OPEN someCursor

WHILE (1 = 1)
BEGIN
    FETCH NEXT FROM someCursor INTO @localVariable // MUST ADD THIS

    IF (@@FETCH_STATUS <> 0)
    BREAK

    // For each row, insert into another table
    INSERT INTO AnotherTable (Column1, Column2) VALUES (3,4)
END
CLOSE someCursor
DEALLOCATE someCursor

Hopefully this will help someone down the road with the same issue I was having.