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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users