Before Insert/Update Trigger

[Thomas Kurz]

Other DBMS support the following construct in a trigger:

CREATE TRIGGER name BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
  SET NEW.column = anyvalue
END;

In SQLite, the NEW record appearently is read-only.

Support for changeable NEW records would however be graceful as it automatically prevents an infinite loop (recursive trigger) that can will more or less automatically occur otherwise when working with additional UPDATE-statements.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[petern]

Thomas, SQLite has the INSTEAD OF trigger to intercept/modify/compose NEW
values:

https://www.sqlite.org/lang_createtrigger.html#instead_of_trigger

I've found most situations are well handled by the INSTEAD OF trigger.   It
is powerful and somewhat comparable in functionality to stored procedure in
other SQL engines.

Since INSTEAD OF is available, the BEFORE trigger comes up only in rare
initialization or validation cases.

Do you have an example where the INSTEAD OF trigger solution is ruled out?

Peter

On Fri, Mar 30, 2018 at 11:50 PM, Thomas Kurz <[hidden email]>
wrote:

> Other DBMS support the following construct in a trigger:
>
> CREATE TRIGGER name BEFORE UPDATE ON table
> FOR EACH ROW
> BEGIN
>   SET NEW.column = anyvalue
> END;
>
> In SQLite, the NEW record appearently is read-only.
>
> Support for changeable NEW records would however be graceful as it
> automatically prevents an infinite loop (recursive trigger) that can will
> more or less automatically occur otherwise when working with additional
> UPDATE-statements.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[petern]

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