sqlite3 - copy autoincrement field to other column on INSERT

Asked Jul 11 '16 at 14:45

Active Jul 11 '16 at 15:29

Viewed 282 times

Is there a more performant way to copy the autoincrement field value to another field after row insert? I'd like to make it automatic with triggers, but I haven't enough knowledge with them to be sure of my code.

this answer (for mysql) doesn't count for it is in the query, and i don't know if it would work with multiple rows

this is the table:

CREATE TABLE IF NOT EXISTS commenti ( 
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  content TEXT COLLATE NOCASE, 
  idcommento INTEGER REFERENCES commenti (id) 
    ON DELETE CASCADE ON UPDATE CASCADE);

and this is the trigger i'm using: it copies id (the autoincrement field) into idcommento only if the value is null in the query.

CREATE TRIGGER set_comment_to_self AFTER INSERT ON commenti
WHEN NEW.idcommento IS NULL 
BEGIN
  UPDATE commenti SET idcommento = NEW.id WHERE id = NEW.id;
END;

I don't know if I can use some sort of easy NEW.idcommento = NEW.id istead of searching every time in all the table...

edited May 23 '17 at 11:58

Community

asked Jul 11 '16 at 14:45

Nereo Costacurta

7,693
4
21
27

You want to copy the auto-increment value to another row in the same table? How is that row identified in order to do the insert/update? – Droppy Jul 11 '16 at 14:48
@Droppy in sqlite3 (as well in mysqlite) using INSERT gives you the NEW object which identify the current inserted row - and its current values. Is this what you wanted to know? – Nereo Costacurta Jul 11 '16 at 14:50
I mean, `NEW` is available only in the `TRIGGER` – Nereo Costacurta Jul 11 '16 at 14:51
No, I understand what an auto-increment column is, however I don't understand where you want this value copied to. – Droppy Jul 11 '16 at 14:53
@Droppy it copies `id` (the autoincrement field) into `idcommento` only if the value is null in the query. it is the same table `commenti`. it is the very same row just added. – Nereo Costacurta Jul 11 '16 at 14:55
The answer is "no". And did you actually measure it? – CL. Jul 11 '16 at 16:17
@CL. what should I had measured? – Nereo Costacurta Jul 11 '16 at 16:34
@CL. well, if i had another solution to benchmark i could do the performance measure, but you said there's no other solution other than that... – Nereo Costacurta Jul 12 '16 at 12:39

sqlite3 - copy autoincrement field to other column on INSERT

0 Answers0