0

I am using SQLite-Net PCL together with SQLite-Net extensions for the development of an application using Xamarin.

I have a one to many relationship between two classes A and B defined as follows:

   public class A
{

    [PrimaryKey, AutoIncrement]
    public int Id
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<B> Sons
    {
        get;
        set;
    }

    public A(string name, List<B> sons)
    {
        Name = name;
        Sons = sons;
    }

}

public class B
{

    [PrimaryKey, AutoIncrement]
    public int Id
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    [ForeignKey(typeof(A))]
    public int FatherId
    {
        get;
        set;
    }

    [ManyToOne]
    public A Father
    {
        get;
        set;
    }

    public B(string name)
    {
        Name = name;
    }

}

I have defined a trigger for automatically setting the LastModified field of both the A and B objects on insertion (and later also on update). The problem is that if I use InsertWithChildren, the trigger does not get activated, while it happens for Insert, even though obviously in this case we do not have the recursive insert.

Example code to replicate the issue:

        var sons1 = new List<B>
        {
            new B("uno"),
            new B("due"),
            new B("tre"),
        };

        one = new A("padre", sons1);

        var sons2 = new List<B>
        {
            new B("uno2"),
            new B("due2"),
            new B("tre2"),
        };

        two = new A("padre2", sons2);

        using (var conn = DatabaseStore.GetConnection())
        {
            conn.DeleteAll<A>();
            conn.DeleteAll<B>();

            string query = @"CREATE TRIGGER  {0}_log AFTER INSERT ON {0} " +
                           "BEGIN " +
                           "UPDATE {0} SET LastModified = datetime('now') " +
                           "WHERE Id = NEW.Id; " +
                           "END;";
            conn.Execute(String.Format(query, "A"));
            conn.Execute(String.Format(query, "B"));
        }

        using (var conn = DatabaseStore.GetConnection())
        {
            conn.InsertWithChildren(one, true);
            conn.Insert(two);
        }

In this example two, inserted with the Insert method, gets the LastModified column correctly updated, while this does not happen for one, which is inserted with InsertWithChildren. Am I doing something in the wrong way?

papafe
  • 2,959
  • 4
  • 41
  • 72
  • Recursive inserts will ultimately call `Insert` as you can see here: https://bitbucket.org/twincoders/sqlite-net-extensions/src/fe7c6539bf56f652aae06d0af26b54cff2ffcfd3/SQLiteNetExtensions/Extensions/WriteOperations.cs?at=master#cl-278 so there shouldn't be any difference – redent84 Mar 17 '15 at 12:54
  • Yes, I have checked the source code, that's why I am baffled at this behaviour. I thought maybe there was some side effect I didn't know of – papafe Mar 17 '15 at 13:00
  • A suggestion is to instead of using trigger, do it in model. Create a wrapper for WriteOperations class that checks if the object implements some interface, such as IModifiable and updates the dates before inserting. – alanh Mar 17 '15 at 19:00
  • That could be a possibility, but that would require more work, and it is something that can be easily implemented as a trigger, so I'm for the easiest solution :) On the bright side, it started to work as soon as I added the same trigger on Update – papafe Mar 18 '15 at 12:32

0 Answers0