1

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

1 Answer 1

1

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>
1
  • It seems I created my problem when using a second trigger instead of modifying the existing one. I thought that would be the more modular approach...
    – U. Windl
    Commented Aug 22, 2023 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.