CODEWITHSUNDEEP
sqlitetriggers
Runeaway3
Runeaway3

Reputation: 1447

How to create SQLite trigger based on existence of value in table?

I want to create a SQLite trigger that will allow me to set the value of a particular property of a row based on the presence or absence of a value in already existing rows. Basically I want an auto incrementation of an integer value (let's say count) for a set of rows that all have the same string value for a particular property (let's say name). However, I want every insert into the table that holds these rows to check if the value for the row about to be inserted's name is already in the table or not. If it is, continue with the auto incrementation from the last row's (with the same name) count. If it does not, start a new auto increment of count for that group. I am new to SQLite but know the general basics. I can create the table:

create table exampleTable(name string, count integer)

I would want an example output (after successive entries of different names) to look like this:

name|count
alek|1
alek|2
kevin|1
blaise|1
alek|3
kevin|2
alek|4

Now I know I can set count to auto increment by just defining it as the primary key, but then it would always just continue to count up by 1 upon every insert regardless of the name value. I believe to do this I need to somehow get the trigger to check previous values so I know the trigger must utilize before insert. This is as much as I know, though. I do not know how to set up, basically, an if/else-type logic on how to restart an auto increment, nor do I know even how to check all/any of the tables previous rows' values of name as a basis for the if/else.

Upvotes: 1

Views: 2682

Answers (1)

CL.
CL.

Reputation: 180040

A trigger cannot change the values to be inserted; the only way to change the count value is to UDPATE it afterwards, i.e., in an AFTER INSERT trigger.

In general, the easiest way to use if/else logic with triggers is to use WHEN clauses:

CREATE TRIGGER count_same_name
AFTER INSERT ON exampleTable
WHEN NEW.count IS NULL
 AND (SELECT count(*)
      FROM exampleTable
      WHERE name = NEW.name
     ) > 1
BEGIN
    UPDATE exampleTable
    SET count = (SELECT MAX(count)
                 FROM exampleTable
                 WHERE name = NEW.name
                ) + 1
    WHERE rowid = NEW.rowid;  -- or whatever the primary key is
END;

CREATE TRIGGER count_new_name
AFTER INSERT ON exampleTable
WHEN NEW.count IS NULL
 AND (SELECT count(*)
      FROM exampleTable
      WHERE name = NEW.name
     ) = 1
BEGIN
    UPDATE exampleTable
    SET count = 1
    WHERE rowid = NEW.rowid;
END;

But in this case, looking up the maximum value would return NULL if no value exists in the table, so you can simply use a single trigger with ifnull():

CREATE TRIGGER exampleTable_count
AFTER INSERT ON exampleTable
WHEN NEW.count IS NULL
BEGIN
    UPDATE exampleTable
    SET count = ifnull((SELECT MAX(count)
                        FROM exampleTable
                        WHERE name = NEW.name
                       ), 0) + 1
    WHERE rowid = NEW.rowid;
END;

Upvotes: 3

Related Questions