I'm trying to implement a string pool using views and triggers: CREATE TABLE StringPool ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Val TEXT UNIQUE );
CREATE TABLE T ( Key TEXT PRIMARY KEY, ValRef INTEGER REFERENCES StringPool(ID) ); CREATE VIEW V AS SELECT Key, Val FROM T LEFT JOIN StringPool ON ValRef = StringPool.ID; CREATE TRIGGER _trg_ii_V INSTEAD OF INSERT ON V BEGIN INSERT OR IGNORE INTO StringPool(Val) VALUES (NEW.Val); INSERT INTO T SELECT NEW.Key, ID FROM StringPool WHERE Val = NEW.Val; END; The approach seemed to be working fine: sqlite> INSERT INTO V VALUES ('K1', 'String1'); sqlite> INSERT INTO V VALUES ('K2', 'String2'); sqlite> SELECT * FROM V; K1|String1 K2|String2 sqlite> SELECT * FROM StringPool; 1|String1 2|String2 ...until I tried a REPLACE instead of a normal INSERT: sqlite> INSERT OR REPLACE INTO V VALUES ('K3', 'String1'); sqlite> SELECT * FROM V; K1| K2|String2 K3|String1 sqlite> SELECT * FROM StringPool; 2|String2 3|String1 The trigger is replacing the ID from the string pool even though I had specifically NOT written a REPLACE in the trigger program. It turns out that this is documented behavior ( http://www.sqlite.org/lang_createtrigger.html ): "An ON CONFLICT <http://www.sqlite.org/lang_conflict.html> clause may be specified as part of an UPDATE <http://www.sqlite.org/lang_update.html> or INSERT <http://www.sqlite.org/lang_insert.html> action within the body of the trigger. However if an ON CONFLICT <http://www.sqlite.org/lang_conflict.html> clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead." ...and that other people have been bitten by it: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01668.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg21206.html So, my questions are: (1) Why was it done this way? Is there an example in which SQLite's behavior here is desirable? If this is indeed a bug, then it seems to be a trivial one to fix: 85432c85432 < pParse->eOrconf = (orconf==OE_Default)?pStep->orconf:(u8)orconf; --- > pParse->eOrconf = (pStep->orconf==OE_Default)?(u8)orconf:pStep->orconf; (2) How, other than changing the SQLite source code, could I make my trigger work the way I want it to? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users