Hi Thomas. Below is a toy "records" table example which illustrates the INSTEAD OF pattern.
----------- CREATE TABLE records(rowid INTEGER PRIMARY KEY, data INTEGER, change_date TEXT DEFAULT CURRENT_TIMESTAMP); CREATE VIEW instead_of_records AS SELECT * FROM records; CREATE TRIGGER instead_of_records_update INSTEAD OF UPDATE ON instead_of_records BEGIN UPDATE records SET (data,change_date)=(NEW.data,CURRENT_TIMESTAMP) WHERE rowid=NEW.rowid; END; --FYI: Only the UPDATE trigger was needed here since the schema supports DEFAULT values. sqlite> INSERT INTO records(data) VALUES(99); sqlite> SELECT * FROM records; rowid,data,change_date 1,99,"2018-04-02 21:52:26" sqlite> UPDATE instead_of_records SET data=100 WHERE rowid=1; sqlite> SELECT * FROM records; rowid,data,change_date 1,100,"2018-04-02 21:53:41" ---------- Regarding http://www.sqlite.org/lang_createtrigger.html The documentation can be very dense and succinct. When in doubt about how something works, do give the command line a try. If you run into a bug or genuine show stopper, please do post it to the list. Peter On Mon, Apr 2, 2018 at 4:36 AM, Thomas Kurz <[email protected]> wrote: > Dear Peter, > > please apologize me replying directly as I wrote my initial post as an > unregistered user and do not know how to reply to the list keeping the > thread reference intact. > > You wrote: > > > SQLite has the INSTEAD OF trigger to intercept/modify/compose NEW values > > This sounds interesting and indeed being what I'm looking for. However, I > must admit that from the explanation of INSTEAD OF trigger I couldn't image > that this could be working. > > This is the actual situation I'm faced with: I want to create this trigger: > > CREATE TRIGGER records_insert BEFORE INSERT/UPDATE ON records FOR EACH ROW > set NEW.change_date:=CURRENT_TIMESTAMP > > The problem: When I implement this with "FOR EACH ROW UPDATE records SET > change_date=CURRENT_TIMESTAMP WHERE id=OLD.id", an infinite recursive loop > occurs. > > Currently, I solve this by using UPDATE OF and specifying all columns but > change_date. It would imho be more elegant to allow the NEW record to be > modified. Unfortunately, I do not see how I could resolve this issue with > an INSTEAD OF trigger. It's a table that is being modified and my > understanding about INSTEAD OF was that they are primarily invented as a > replacement for updateable views. > > Kind regards, > Thomas > > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
