SQLite Forum

In a trigger insert all columns of NEW into another table
Login

In a trigger insert all columns of NEW into another table

(1) By anonymous on 2021-11-30 02:53:28 [source]

The goal is to basically make a backup log table.

If I write something like

CREATE TRIGGER mytrigger AFTER INSERT ON table
BEGIN
   INSERT INTO backup SELECT * FROM NEW;
END

it fails on insert with error "no such table: main.NEW"

I know I can list all the columns manually but then I will need to update the trigger every time I change scheme of the tables.

Is there a beautiful way of doing this?

(2) By Stephan Beal (stephan) on 2021-11-30 02:59:14 in reply to 1 [link] [source]

I know I can list all the columns manually but then I will need to update the trigger every time I change scheme of the tables.

Don't forget that even if your desired syntax worked, you would still have to change your backup table even time the source table changes, otherwise the trigger would fail.

One way or the other, you have to account for those changes somewhere.

(3) By anonymous on 2021-11-30 03:13:43 in reply to 2 [link] [source]

I can sort of solve it with a command

CREATE TABLE backup AS SELECT * FROM table WHERE 0

(4) By Gunter Hick (gunter_hick) on 2021-11-30 08:08:44 in reply to 1 [link] [source]

NEW is not a table, it is an alias for the row being inserted.

INSERT INTO backup VALUES (NEW.field,...);

And be carefuly to modifiy the structures of both tables and the VALUES list on every change.

Alternatively, this may work, but with an extra scan:

INSERT INTO backup SELECT * FROM table WHERE table.rowid = NEW.rowid;

Or replace the WHERE clause by a complete comparison of the PRIMARY KEY on a WITHOUT ROWID table.

NOTE: Omitting the WHERE clase will cause ALL of the records to be copied for EACH insert, which is maybe not what is desired.

(5) By anonymous on 2021-11-30 10:50:29 in reply to 4 [link] [source]

So in other words no beautiful solution for this simple and straightforward problem. I am the first person ever wanted to extract a row column names in a trigger and no one ever needed to copy rows to a backup/history/log table in a trigger.

(6) By ddevienne on 2021-11-30 11:15:17 in reply to 5 [link] [source]

Not at all. But many people frown on SELECT *, and prefer to list columns explicitly.

SQLite has no concept of row-values, like PostgreSQL does.
So NEW and OLD in triggers are not row-values like they are in PostgreSQL.

Even a table alias is basically a row-value in PostgreSQL, and defining a table
implicitly defines an equivalent composite type, albeit w/o the constraints.
(which you can manually re-add via a domain type over the composite type.)

(7) By Gunter Hick (gunter_hick) on 2021-11-30 11:22:00 in reply to 5 [link] [source]

Yes, maybe you should take out a patent on your idea.

Being rude under the cover of anonimity is not likely to attract helpful responses.