title: 'no such function' error on sqlite UPDATE
body: <p><strong>Summary:</strong> 
  I am trying to update a database that contains metadata for many scientific articles. In particular, the database is for the <a href="http://www.papersapp.com/mac/" rel="nofollow">Papers</a> application. My problem is that when I try to <code>UPDATE</code> a table, I get </p>
  
  <p><code>Error: no such function: enableTransactionLog</code>. </p>
  
  <p>What is the problem? If you don't know, can you point me to where I should look for debugging? I've done a lot of searching on SO and Google, but I can't find anything that helps me.</p>
  
  <p><strong>More background:</strong></p>
  
  <p>An overview of my table:</p>
  
  <pre><code>sqlite&gt; PRAGMA table_info(Publication);
  0|ROWID|INTEGER|0||1
  1|abbreviation|TEXT|0||0
  2|accepted_date|TEXT|0||0
  3|attributed_subtitle|TEXT|0||0
  4|attributed_title|TEXT|0||0
  5|author_string|TEXT|0||0
  6|author_year_string|TEXT|0||0
  7|body|TEXT|0||0
  8|bundle|TEXT|0||0
  9|bundle_string|TEXT|0||0
  10|canonical_title|TEXT|0||0
  11|citekey|TEXT|0||0
  
  ...I cut off the last ~50 lines of output
  </code></pre>
  
  <p>My command:</p>
  
  <pre><code>sqlite&gt; UPDATE Publication SET citekey=NULL;
  Error: no such function: enableTransactionLog
  </code></pre>
  
  <p>The schema.</p>
  
  <pre><code>CREATE TABLE "Publication" (ROWID INTEGER PRIMARY KEY, abbreviation TEXT, accepted_date TEXT, attributed_subtitle TEXT, attributed_title TEXT, author_string TEXT, author_year_string TEXT, body TEXT, bundle TEXT, bundle_string TEXT, canonical_title TEXT, citekey TEXT, citekey_base TEXT, copyright TEXT, created_at DOUBLE, document_number TEXT, doi TEXT, draft INTEGER, editor_string TEXT, endpage TEXT, extensible_properties BLOB, factor FLOAT, flagged INTEGER, full_author_string TEXT, full_editor_string TEXT, full_photographer_string TEXT, full_translator_string TEXT, imported_date DOUBLE, initial TEXT, institution TEXT, keyword_string TEXT, kind TEXT, label INTEGER, language TEXT, lastread_date DOUBLE, location TEXT, manuscript INTEGER, marked_deleted INTEGER, marked_duplicate INTEGER, marked_edited INTEGER, matched INTEGER, newly_added INTEGER, notes TEXT, number TEXT, open_access INTEGER, photographer_string TEXT, place TEXT, printed_date DOUBLE, privacy_level INTEGER, publication_count INTEGER, publication_date TEXT, publication_string TEXT, publisher TEXT, quality INTEGER, rating INTEGER, read_status INTEGER, refreshed_at DOUBLE, revision_date TEXT, searchresult INTEGER, startpage TEXT, status TEXT, submission_date TEXT, subtitle TEXT, subtype INTEGER, summary TEXT, tag_string TEXT, times_cited INTEGER, times_read INTEGER, title TEXT, translator_string TEXT, type INTEGER, update_count INTEGER, updated_at DOUBLE, user_label TEXT, uuid TEXT UNIQUE NOT NULL, version TEXT, volume TEXT, FOREIGN KEY (bundle) REFERENCES Publication (uuid) ON DELETE RESTRICT);
  CREATE INDEX Publication_abbreviation_IDX ON Publication (abbreviation);
  CREATE INDEX Publication_accepted_date_IDX ON Publication (accepted_date);
  CREATE INDEX Publication_citekey_IDX ON Publication (citekey);
  CREATE INDEX Publication_citekey_base_IDX ON Publication (citekey_base);
  CREATE INDEX Publication_created_at_IDX ON Publication (created_at);
  CREATE INDEX Publication_doi_IDX ON Publication (doi);
  CREATE INDEX Publication_endpage_IDX ON Publication (endpage);
  CREATE INDEX Publication_factor_IDX ON Publication (factor);
  CREATE INDEX Publication_imported_date_IDX ON Publication (imported_date);
  
  about 50 lines of output deleted here
  
  CREATE INDEX Publication_version_IDX ON Publication (version);
  CREATE INDEX Publication_volume_IDX ON Publication (volume);
      CREATE TRIGGER _del_log_Publication AFTER DELETE ON Publication WHEN enableTransactionLog() AND (OLD."searchresult" &lt;&gt; 1 AND NOT (OLD."type" &lt; 0 AND OLD."type" &gt; -1000)) BEGIN
      INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", OLD."uuid", 1, NULL, NULL, device(), dbRevision());END;
  CREATE TRIGGER _del_log_Publication_Bundle AFTER DELETE ON Publication WHEN enableTransactionLog() AND (OLD."searchresult" &lt;&gt; 1 AND (OLD."type" &lt; 0 AND OLD."type" &gt; -1000)) BEGIN
      INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", OLD."uuid", 1, "abbreviation,accepted_date,attributed_subtitle,attributed_title,body,bundle,canonical_title,citekey,citekey_base,copyright,created_at,document_number,doi,draft,endpage,extensible_properties,factor,flagged,imported_date,initial,institution,kind,label,language,lastread_date,location,manuscript,marked_deleted,marked_duplicate,marked_edited,matched,newly_added,notes,number,open_access,place,printed_date,privacy_level,publication_date,publication_string,publisher,quality,rating,read_status,refreshed_at,revision_date,searchresult,startpage,status,submission_date,subtitle,subtype,summary,tag_string,times_cited,times_read,title,type,updated_at,user_label,uuid,version,volume", quote(OLD."abbreviation")||','||quote(OLD."accepted_date")||','||quote(OLD."attributed_subtitle")||','||quote(OLD."attributed_title")||','||quote(OLD."body")||','||quote(OLD."bundle")||','||quote(OLD."canonical_title")||','||quote(OLD."citekey")||','||quote(OLD."citekey_base")||','||quote(OLD."copyright")||','||quote(OLD."created_at")||','||quote(OLD."document_number")||','||quote(OLD."doi")||','||quote(OLD."draft")||','||quote(OLD."endpage")||','||quote(OLD."extensible_properties")||','||quote(OLD."factor")||','||quote(OLD."flagged")||','||quote(OLD."imported_date")||','||quote(OLD."initial")||','||quote(OLD."institution")||','||quote(OLD."kind")||','||quote(OLD."label")||','||quote(OLD."language")||','||quote(OLD."lastread_date")||','||quote(OLD."location")||','||quote(OLD."manuscript")||','||quote(OLD."marked_deleted")||','||quote(OLD."marked_duplicate")||','||quote(OLD."marked_edited")||','||quote(OLD."matched")||','||quote(OLD."newly_added")||','||quote(OLD."notes")||','||quote(OLD."number")||','||quote(OLD."open_access")||','||quote(OLD."place")||','||quote(OLD."printed_date")||','||quote(OLD."privacy_level")||','||quote(OLD."publication_date")||','||quote(OLD."publication_string")||','||quote(OLD."publisher")||','||quote(OLD."quality")||','||quote(OLD."rating")||','||quote(OLD."read_status")||','||quote(OLD."refreshed_at")||','||quote(OLD."revision_date")||','||quote(OLD."searchresult")||','||quote(OLD."startpage")||','||quote(OLD."status")||','||quote(OLD."submission_date")||','||quote(OLD."subtitle")||','||quote(OLD."subtype")||','||quote(OLD."summary")||','||quote(OLD."tag_string")||','||quote(OLD."times_cited")||','||quote(OLD."times_read")||','||quote(OLD."title")||','||quote(OLD."type")||','||quote(OLD."updated_at")||','||quote(OLD."user_label")||','||quote(OLD."uuid")||','||quote(OLD."version")||','||quote(OLD."volume"), device(), dbRevision());END;
  
  I cutoff the rest of the output
  </code></pre>
  
  <p>I'm using the <code>sqlite3</code> that shipped with my MacBook Pro. If you're interested, I'm trying to accomplish the task described in the third post of <a href="http://support.mekentosj.com/discussions/suggestions/1942-universal-citekeys-discussion-and-questions" rel="nofollow">this discussion</a>.</p>
  
  <p><strong>The solution (after Dan D. got me 99.9% of the way there):</strong></p>
  
  <pre><code>sqlite&gt; DROP TRIGGER _upd_log_Publication_citekey;
  sqlite&gt; UPDATE Publication SET citekey = NULL;
  sqlite&gt; CREATE TRIGGER _upd_log_Publication_citekey AFTER UPDATE OF citekey ON Publication
     ...&gt;     WHEN enableTransactionLog() AND (NEW."searchresult" = 0) AND (OLD."citekey" IS NULL AND NEW."citekey" IS NOT NULL OR OLD."citekey" IS NOT NULL AND NEW."citekey" IS NULL OR OLD."citekey" &lt;&gt; NEW."citekey") BEGIN
     ...&gt;     DELETE FROM changeLog WHERE modUUID = NEW."uuid" AND modColumn="citekey" AND modType=2; -- delete old changes immediately, for cleanup
     ...&gt; INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", NEW."uuid", 2, "citekey", NEW."citekey", device(), dbRevision());END;
  </code></pre>