The conflict resolution method of the outside statement applies to the trigger program. Since you have specified the "REPLACE" conflict resolution mechanism in the outer statement, the "IGNORE" conflict resolution specification in the trigger program is converted to the outer "REPLACE" specification. As a result, the "INSERT OR REPLACE" replaces the counter_table row rather than ignoring the insert.

The easiest way to fix this is to use an "upsert" in the trigger:

create trigger trigger1 after insert on contacts  
begin  
    insert into counter_table values (10, 1) on conflict (id) do update set counter=counter+1;  
end;

or to write the trigger in a fashion that does not require conflict resolution:

create trigger trigger1 after insert on contacts
begin
    insert into counter_table 
         select 10, 0
          where not exists (select * 
                              from counter_table 
                             where id == 10);
    update counter_table set counter=counter+1 where id == 10;
end;

Thank you for the explanation. I was unable to find this detail about the conflict resolution in the manual.

Jan

It is the 8th paragraph of the documentation for the CREATE TRIGGER statement found here https://sqlite.org/lang_createtrigger.html which says:

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

whwre ON CONFLICT is https://sqlite.org/lang_conflict.html as in insert or REPLACE / insert or IGNORE / insert or ABORT

If "DO NOTHING" is the same as "IGNORE" then why does the first trigger statement fail and the second works?

create table t1(i integer primary key);
create table t2(i integer primary key,c1);
create trigger t2_au after update of c1 on t2 begin
   insert or ignore into t1(i)values(new.i);
-- insert into t1(i)values(new.i)on conflict(i)do nothing;
end;
insert into t2(i,c1)values(1,1);
insert into t2(i,c1)values(1,2)on conflict(i)do update set(c1)=(excluded.c1);
insert into t2(i,c1)values(1,1)on conflict(i)do update set(c1)=(excluded.c1);

No one cares about this long standing UPSERT bug?

DO NOTHING is not a statement conflict resolution method, it is a row conflict resolution method. By avoiding the row conflict you have avoided a statement level conflict, so the statement level conflict resolution is never exercised.

That is, you can have BOTH a statement resolution method AND a row conflict resolution method at the same time:

INSERT or REPLACE into t values (...) on conflict (...) do ...

It is only if the conflict is not resolved by the row (or upsert, which is a terrible name) on conflict clauses, that it is left to be resolved by the statement conflict resolution method in effect.

That is to say that if you do not catch the error in the statement and handle it, that it will then percolate up and abort your whole program.

Just as a spelling mistake in a single word in a single sentence can be addressed either at the word level, the sentence level, the paragraph level, the chapter level, or the book level (presumably by tossing the whole thing in the dustbin), so to is there a hierarchy of error resolution methods that were introduced with the (god I hate that name) the upsert.

This is not entirely true because a hierarchy of conflict resolution methods has always existed, and if a "lower down" resolution method "resolved" the conflict then that conflict was not seen by "higher level" conflict resolution handlers because, well, there was nought left to resolve.

For example you can declare a conflict resolution clause of a constraint, such as a column NOT NULL constraint in a table declaration and if the method is REPLACE then the value of the column is REPLACED with the DEFAULT, which may very well resolve the conflict so that no "bubbling up" is required.

The gorilla in this discussion is that none of this is "standard" SQL. Define it as you will.

In ON CONFLICT we have a warning about your point:

Do not confuse these two separate uses of the "ON CONFLICT" phrase.

But does not clearly explain the effects confusion can cause.

Further on, the description of IGNORE algorithm includes:

... the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong.

Sounds like a "row level" conflict. However, as you note:

...a hierarchy has always existed. And, if a "lower down" resolution method "resolved" the conflict...

So why wouldn't an IGNORE at the statement level in a trigger (sub program?) not resolve the "one row that contains the constraint violation"?

To take it to the detail, up till the latest (yet unreleased) UPSERT changes my problem was fixed by this simple change in upsert.c:

• pUpsert->pUpsertWhere, OE_Abort, 0, 0, pUpsert);
• pUpsert->pUpsertWhere, OE_Default, 0, 0, pUpsert);

The UPSERT forced the lower conflict handling to ABORT, always. Changing it to Default is a simple "fix", but not complete. When I first reported this I added (on 4/15/2019):

A gentle suggestion. Add to the grammar of the UPSERT clause, after DO UPDATE an ON CONFLICT section to specify the CONFLICT policy of the UPDATE (which, obviously can't be the same as the CONFLICT policy on the top level INSERT).

My hope was that check-in 2020-12-14 [6b01a24daa] was my long awaited "fix". However it solves a different problem, allowing multiple conflict targets. And, not surprisingly, invalidates my simple fix, above. (I have yet to go through the new code looking were to put my "fix".)