I was surprised when the following SQL failed:

create table t1(c1 unique not null, c2 not null);
create table t2(c1 unique not null);

create trigger t1_ai after insert on t1
begin
    insert or ignore into t2 (c1) values (new.c2);
end;

create trigger t1_au after update on t1 when new.c2 != old.c2
begin
    insert or ignore into t2 (c1) values (new.c2);
end;

insert into t1 (c1, c2) values ('one','test_one');
insert into t1 (c1, c2) values ('two','test_two');
insert into t1 (c1, c2) values ('three','test_one');

insert into t1(c1, c2) values ('one', 'test_two')
on conflict (c1) do update set c2 = excluded.c2;

The error is Runtime error near line 18: UNIQUE constraint failed: t2.c1 (19). I'm using SQLite version 3.39.0 2022-06-25 14:57:57.

The documentation for triggers states that

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

In this case the conflict resolution in the trigger applies to a different table (t2) than the upsert (t1), which is what led to my surprise.

Is there a different way to perform database housekeeping in triggers while using upsert without knowing the details of how the triggers are implemented?

Perhaps instead of the "or ignore" in the triggers, you could re-define them to first check if the value's in there before trying the insert?

create trigger t1_ai after insert on t1
when not exists(select 1 from t2 where c1 = new.c2)
begin
    insert into t2 (c1) values (new.c2);
end;

create trigger t1_au after update on t1
when new.c2 != old.c2
and not exists(select 1 from t2 where c1 = new.c2)
begin
    insert into t2 (c1) values (new.c2);
end;