mysql 触发器 避免递归_插入INSERT触发器时，触发递归级别太多(too many levels of trigger recursion when inserting in an INSER...

I want to create a UniqueIdentifier for each new row created or inserted in a SQLite3 table. I have searched for solutions here on this forum and I found two "Select Statements" that create such a UniqueIdentifier. The code is as following:

SELECT substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||

'-'||v||substr(u,17,3)||'-'||substr(u,21,12) from (

select lower(hex(randomblob(16))) as u, substr('89ab',random() % 4 + 1, 1) as v);

When I enter the SQL in Firefox SQLite Manager it works just fine a delivers the value I want to use.

I tried to use that created value into a table and it does not work. I always get the error message "Too many recursions..."

I used the following SQL code to create the table which worked just fine.

CREATE TABLE "SampleTable" (

"SampleTableID" integer PRIMARY KEY AUTOINCREMENT NOT NULL ,

"SampleTableUID" varchar(32) UNIQUE NOT NULL,

"Name" varchar(50),

)

Then I tried to use the following SQL code to create the Trigger and that did not work. In one of my trials I create a data record first and then I created the trigger and it seems to work when I add a second data record.

CREATE TRIGGER "UID" AFTER INSERT ON "SampleTable" FOR EACH ROW BEGIN INSERT INTO "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) from (

select lower(hex(randomblob(16))) as u, substr('89ab',random() % 4 + 1, 1) as v); END

What do I do wrong?

I want to create this UniqueIdentifier with the very first data record and with all following records.

CORRECT CODE IS AS FOLLOWING

Thanks to the help of CL I found the answer myself. The correct 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"

FOR 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) from (

select lower(hex(randomblob(16))) as u, substr('89ab',random() % 4 + 1, 1) as v))

WHERE rowid = last_insert_rowid();

END

INSERT INTO "SampleTable" ("Name") VALUES ("Name1");

Thanks!