How can I conditionally prevent a column from being updated in SQLite?

Question

I have a table with CREATED and MODIFIED columns. I only want to insert the CREATED value once and have it be thereafter immutable. I know how to do this in a tedious way (write a "DoesRecordExist()" method and then alter the query and number of query parameters based on that), but surely there is a slicker way to accomplish this. After all, this has to be a common requirement (a "database pattern" if you will).

My code is this:

public void InsertUserSiteRecord(UserSite us)
{
    using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            cmd.CommandText =
                String.Format(
                    @"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Created, Modified) 
                                VALUES (@SiteNum, @SerialNum, @UserName, @Created, @Modified)");
            cmd.Parameters.Add(new SQLiteParameter("SiteNum", us.SiteNum));
            cmd.Parameters.Add(new SQLiteParameter("SerialNum", us.SerialNum));
            cmd.Parameters.Add(new SQLiteParameter("UserName", us.UserName));
            cmd.Parameters.Add(new SQLiteParameter("Created", us.Created));
            cmd.Parameters.Add(new SQLiteParameter("Modified", us.Modified));

            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}

...and I want to avoid having to do something like this:

public void InsertUserSiteRecord(UserSite us)
{
    using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
    {
        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        {
            if (!RecordExists(us.SiteNum, us.SerialNum, us.UserName))
            {
                cmd.CommandText =
                    String.Format(
                        @"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Created, Modified) 
                                    VALUES (@SiteNum, @SerialNum, @UserName, @Created, @Modified)");
            else
            {
                cmd.CommandText =
                    String.Format(
                        @"INSERT INTO UserSite (SiteNum, SerialNum, UserName, Modified) 
                                    VALUES (@SiteNum, @SerialNum, @UserName, @Modified)");
            }
            cmd.Parameters.Add(new SQLiteParameter("SiteNum", us.SiteNum));
            cmd.Parameters.Add(new SQLiteParameter("SerialNum", us.SerialNum));
            cmd.Parameters.Add(new SQLiteParameter("UserName", us.UserName));
            if (!RecordExists(us.SiteNum, us.SerialNum, us.UserName))
            {
                cmd.Parameters.Add(new SQLiteParameter("Created", us.Created));
            }
            cmd.Parameters.Add(new SQLiteParameter("Modified", us.Modified));

            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}

private bool RecordExists(String SiteNum, String SerialNum, String UserId)
{
    // query the table to see if those three values exist in any record
}

Is there a SQL[ite] construct that is something like:

cmd.Parameters.AddOnlyIfColumnIsEmpty(new SQLiteParameter("Created", us.Created));

? Or how can this be best tackled?

Do you expect a scenario where the CREATED column would be null? — Tarik, Feb 10 '15 at 22:02

score 2 · Answer 1 · edited Oct 10 '22 at 04:17

You may be able to accomplish this using a trigger. See the SQLite documentation on triggers here: https://www.sqlite.org/lang_createtrigger.html

Basically, you would create an INSTEAD OF trigger, then set up your query accordingly. From the documentation:

For an example of an INSTEAD OF trigger, consider the following schema:

CREATE TABLE customer(
  cust_id INTEGER PRIMARY KEY,
  cust_name TEXT,
  cust_addr TEXT
);
CREATE VIEW customer_address AS
   SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
BEGIN
  UPDATE customer SET cust_addr=NEW.cust_addr
   WHERE cust_id=NEW.cust_id;
END;
With the schema above, a statement of the form:

UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_id;

Causes the customer.cust_addr field to be updated for a specific customer entry that has customer.cust_id equal to the $cust_id parameter. Note how the values assigned to the view are made available as field in the special "NEW" table within the trigger body.

What you would want to do is just set up the trigger to not update that column even if the original query passes in that column to be updated.

Great answer, but I try to keep my code "Grammatik" score low (keep the reading grade level as low as possible, for future maintenance). The Excedrin I save may be my own. — B. Clay Shannon-B. Crow Raven, Feb 10 '15 at 22:37

score 2 · Accepted Answer · answered Feb 10 '15 at 22:04

You need to check for existance of the record; then do an UPDATE if it exists (not changing your Created value), and an INSERT if it doesn't.

Your original code will give a duplicate key error, assuming you have a PK on the table.

How can I conditionally prevent a column from being updated in SQLite?

2 Answers2