insert trigger to auto-set key fields for composite primary key

Question

Basically the next step for Error "UNIQUE constraint failed" with composite primary key: I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one. Basically:

CREATE TRIGGER TRG_NAMES_ADD2 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.SEQ < 1
BEGIN
    UPDATE NAMES SET
        SEQ = (SELECT IFNULL(MAX(SEQ), 0) + 1 FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

However I'd like to do the same thing for TYPE independently from SEQ. The problems I found were that I can have only one UPDATE per trigger, and the AFTER INSERT triggers may be executed in any order.

I tried a second trigger like this:

CREATE TRIGGER TRG_NAMES_ADD1 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.TYPE < 1
BEGIN
    UPDATE NAMES SET
        TYPE = (SELECT IFNULL(MAX(TYPE), 0) + 1 FROM NAMES),
        SEQ = 1
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

However for completeness TRG_NAMES_ADD2 should run after TRG_NAMES_ADD1, or I would have to put both actions (set TYPE to a possibly new value (if < 1), and then use that new value to possibly set a new value for SEQ (if < 1), too) into one trigger.

Unfortunately that's beyond my capabilities.

For convenience, here's the table creation and some sample inserts:

CREATE TABLE NAMES (
TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20),
PRIMARY KEY (TYPE, SEQ)
);
insert into NAMES (type, seq, name) values (2, 3, "T1");
insert into NAMES (type, seq, name) values (2, 0, "T2");
insert into NAMES (type, seq, name) values (2, 0, "T3");
insert into NAMES (type, seq, name) values (0, 1, "T4");
insert into NAMES (type, seq, name) values (0, 0, "T5");

So the assignments should be like this:

T|S|N
-----
2|3|T1  # T,S set manually
2|4|T2  # S set automatically
2|5|T3  # S set automatically
3|1|T4  # T set automatically
4|1|T5  # T,S set automatically

score 1 · Accepted Answer · edited Aug 23 '23 at 06:09

Are you certain that you can have only one update per trigger? I didn't find any reference to this limit in the sqlite docs.

Anyway, you can just combine both updates in a single one:

CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.TYPE < 1 OR NEW.SEQ < 1
BEGIN
    UPDATE NAMES SET
        TYPE = (SELECT CASE WHEN NEW.TYPE < 1 THEN IFNULL(MAX(TYPE), 0) + 1 ELSE NEW.TYPE END FROM NAMES),
        SEQ = (SELECT CASE WHEN NEW.SEQ < 1 THEN IFNULL(MAX(SEQ), 0) + 1 ELSE NEW.SEQ END FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

Verification session:

> sqlite3 trigger.sqlite
SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
sqlite> CREATE TABLE NAMES (
   ...> TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20),
   ...> PRIMARY KEY (TYPE, SEQ)
   ...> );
sqlite> CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES FOR EACH ROW
   ...> WHEN NEW.TYPE < 1 OR NEW.SEQ < 1
   ...> BEGIN
   ...>     UPDATE NAMES SET
   ...>         TYPE = (SELECT CASE WHEN NEW.TYPE < 1 THEN IFNULL(MAX(TYPE), 0) + 1 ELSE NEW.TYPE END FROM NAMES),
   ...>         SEQ = (SELECT CASE WHEN NEW.SEQ < 1 THEN IFNULL(MAX(SEQ), 0) + 1 ELSE NEW.SEQ END FROM NAMES WHERE TYPE = NEW.TYPE)
   ...>     WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
   ...> END;
sqlite> insert into NAMES (type, seq, name) values (2, 3, "T1");
sqlite> insert into NAMES (type, seq, name) values (2, 0, "T2");
sqlite> insert into NAMES (type, seq, name) values (2, 0, "T3");
sqlite> insert into NAMES (type, seq, name) values (0, 1, "T4");
sqlite> insert into NAMES (type, seq, name) values (0, 0, "T5");
sqlite> select * from names;
2|3|T1
2|4|T2
2|5|T3
3|1|T4
4|1|T5
sqlite>

insert trigger to auto-set key fields for composite primary key

1 Answers1