CODEWITHSUNDEEP
sqliteloopsvariablessql-insert
Dan Wright
Dan Wright

Reputation: 35

SQLite work around for Loops and Variable to allow varying N number of INSERT statements in a Trigger Statement

I have two tables

I have a LineUp table

Fixture Number_Sub Complete
1 1 TRUE
2 3 TRUE

When I change Complete to TRUE for example on row 2 I want to use a Trigger to INSERT 3 lines in to table Sub ...

Fixture
1

...with the corresponding fixture number - it would look like this.....

Fixture
1
2
2
2

The number in LineUp.Number_Sub is variable.

I am using Sqlite on a Mac and I know I can't use variables or loops but I am new to SQL and don't know where to start.

If someone could point me in the right direction I'd really appreciate the help.

Also any tips on better wording of question would be great as I am new to this forum.

Upvotes: 0

Views: 774

Answers (1)

forpas
forpas

Reputation: 164139

You need an AFTER UPDATE trigger for the table LineUp.
This trigger should, somehow, do a loop, from 1 to the value of Number_Sub and each time insert a row in the table Sub.
Unfortunately SQLite does not support loop constructs and the only way to simulate this behavior would be a recursive CTE.
Unfortunately (again) SQLite does not allow CTEs inside a trigger.

A workaround, is to create a table, say Numbers, with 1 column number and rows with values 1-100 (or the max value that can be stored in Number_Sub):

CREATE TABLE Numbers AS
WITH cte AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1 FROM cte WHERE number < 100
)
SELECT * FROM cte;

Now, you can create the trigger, which will insert into Sub as many rows as the value of Number_Sub:

CREATE TRIGGER trg_LineUp_upd AFTER UPDATE OF Complete ON LineUp
WHEN NEW.Complete = 'TRUE'
BEGIN
  INSERT INTO Sub(Fixture)
  SELECT NEW.Fixture
  FROM Numbers
  WHERE number <= NEW.Number_Sub;
END;

See the demo.

I assumed that the value TRUE in the column Complete of the table LineUp is a string.
If it is a Boolean (Integer) value, then change WHEN NEW.Complete = 'TRUE' to WHEN NEW.Complete.

Upvotes: 1

Related Questions