sqlite - too many levels of trigger recursion when inserting in an INSERT trigger -

sqlite - too many levels of trigger recursion when inserting in an INSERT trigger -

i want create uniqueidentifier each new row created or inserted in sqlite3 table. have searched solutions here on forum , found 2 "select statements" create such uniqueidentifier. code following:

select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)|| '-'||v||substr(u,17,3)||'-'||substr(u,21,12) ( select lower(hex(randomblob(16))) u, substr('89ab',random() % 4 + 1, 1) v);

when come in sql in firefox sqlite manager works fine delivers value want use.

i tried utilize created value table , not work. error message "too many recursions..."

i used next sql code create table worked fine.

create table "sampletable" ( "sampletableid" integer primary key autoincrement not null , "sampletableuid" varchar(32) unique not null, "name" varchar(50), )

then tried utilize next sql code create trigger , did not work. in 1 of trials create info record first , created trigger , seems work when add together sec info record.

create trigger "uid" after insert on "sampletable" each row begin insert "sampletable" ("sampletableuid") select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)|| '-'||v||substr(u,17,3)||'-'||substr(u,21,12) ( select lower(hex(randomblob(16))) u, substr('89ab',random() % 4 + 1, 1) v); end

what do wrong?

i want create uniqueidentifier first info record , next records.

correct code following

thanks help of cl found reply myself. right working solution is:

create table "sampletable" ( "sampletableid" integer primary key autoincrement not null , "sampletableuid" varchar(32), "name" varchar(50) ) create trigger "uid" after insert on "sampletable" each row begin update "sampletable" set "sampletableuid" = (select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)|| '-'||v||substr(u,17,3)||'-'||substr(u,21,12) ( select lower(hex(randomblob(16))) u, substr('89ab',random() % 4 + 1, 1) v)) rowid = last_insert_rowid(); end insert "sampletable" ("name") values ("name1");

thanks!

the insert statement adds new record table.

to alter in inserted row, must utilize update statement:

create trigger uid after insert on sampletable each row when new.sampletableuid null begin update sampletable set sampletableuid = (select whatever...) sampletableid = new.sampletableid; end;

sqlite sqlite3 triggers