CODEWITHSUNDEEP
sqlitetriggers
alphaService
alphaService

Reputation: 131

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

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!

Upvotes: 1

Views: 1597

Answers (1)

CL.
CL.

Reputation: 180161

The INSERT statement adds a new record to the table.

To change something in the inserted row, you must use an UPDATE statement:

CREATE TRIGGER UID
AFTER INSERT ON SampleTable
FOR EACH ROW
WHEN NEW.SampleTableUID IS NULL
BEGIN
    UPDATE SampleTable
    SET SampleTableUID = (SELECT whatever...)
    WHERE SampleTableID = NEW.SampleTableID;
END;

Upvotes: 1

Related Questions