如何有条件地阻止在SQLite中更新列

Question

我有一个包含CREATED和MODIFIED列的表。我只想插入CREATED值一次，然后让它不可变。我知道如何以乏味的方式做到这一点（写一个"DoesRecordExist（）"）；方法，然后在此基础上更改查询和查询参数的数量），但肯定有一种更巧妙的方法可以实现这一点。毕竟，这必须是一个常见的需求（如果你愿意的话，这是一个"数据库模式"）。

我的代码是：

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();
    }
}

我想避免做这样的事情：

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
}

是否存在类似于的SQL[ite]构造

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

或者如何才能最好地解决这一问题？

Answer 1

您需要检查记录是否存在；如果它存在，则执行UPDATE（不更改Created值），如果没有，则执行INSERT。

假设表上有PK，您的原始代码将给出一个重复的密钥错误。

Answer 2

您可以使用trigger来完成此操作。请参阅此处的SQLite触发器文档：https://www.sqlite.org/lang_createtrigger.html

基本上，您将创建一个INSTEAD OF触发器，然后相应地设置查询。来自文件：

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;

使customer.cust_addr字段为customer.cust_id等于$cust_id参数的特定客户条目更新。注意分配给视图的值是如何作为特殊的"视图"中的字段可用的；NEW"；触发器主体内的表。

您想要做的只是将触发器设置为不更新该列，即使原始查询传入要更新的列。