SQLite User Forum

Insert or ignore run into an trigger replace a row.
Login

Insert or ignore run into an trigger replace a row.

(1) By anonymous on 2025-01-10 11:21:13 [link] [source]

Hello. An insert or replace statement start a trigger (after update) with an insert or ignore inside replace values. I think is a mistake.

Exmaple:

sqlite> create table p1 (id integer primary key, col text);
sqlite> create table p2 (id integer primary key, col text);
sqlite> create trigger trg_p1 after insert on p1 for each row begin insert or ignore into p2 values (new.id, new.col || ' otro'); end;
sqlite> insert or replace into p1 values (1, "valor");
sqlite> select * from p2
   ...> ;
id  col
--  ----------
1   valor otro
sqlite> insert or replace into p1 values (1, "patata");
sqlite> select * from p2;
id  col
--  -----------
1   patata otro

No modification in p2 table is expected.

sqlite version:
sqlite> .version
SQLite 3.47.2 2024-12-07 20:39:59 2aabe05e2e8cae4847a802ee2daddc1d7413d8fc560254d93ee3e72c14685b6c
gcc-4.4.7 20120313 (Red Hat 4.4.7-23) (32-bit)

Thanks in advace.
Isaac.

(2) By Adrian Ho (lexfiend) on 2025-01-10 12:38:06 in reply to 1 [link] [source]

It's documented behaviour:

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.

(3) By anonymous on 2025-01-10 13:41:04 in reply to 2 [link] [source]

The action within the body has ON CONFLICT clause.

In documentation:

The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that the syntax reads more naturally. For example, instead of "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause is the same either way.

(see https://sqlite.org/lang_conflict.html)

The insert has this clause:

create trigger trg_p1 after insert on p1 for each row begin insert or ignore into p2 values (new.id, new.col ' otro'); end;

Thanks. Isaac

(4.1) By Adrian Ho (lexfiend) on 2025-01-11 03:21:48 edited from 4.0 in reply to 3 [link] [source]

When the documentation says:

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.

the "statement causing the trigger to fire" is your top-level INSERT OR REPLACE.

You might be confused about what the "outer statement" means, but if you recall that CREATE TRIGGER cannot specify conflict resolution (i.e. CREATE TRIGGER ... AFTER INSERT OR IGNORE ON ... is a syntax error), the only other possibility is the "statement causing the trigger to fire", which is why your INSERT OR IGNORE in the trigger effectively became INSERT OR REPLACE.

Hence the result of:

create table p1 (id integer primary key, col text);
create table p2 (id integer primary key, col text);
create table p3 (id integer primary key, col text);
create table p4 (id integer primary key, col text);
create trigger trg_p2 after insert on p1 for each row begin insert or ignore into p2 values (new.id, new.col || ' otro'); end;
create trigger trg_p3 after insert on p1 for each row begin insert or replace into p3 values (new.id, new.col || ' ohno'); end;
create trigger trg_p4 after insert on p1 for each row begin insert into p4 values (new.id, new.col || ' iffy'); end;
create trigger trg_p4_1 after insert or replace on p1 for each row begin insert into p4 values (new.id, new.col || ' kayo'); end;
insert or replace into p1 values (1, 'valor');
select p1.*, p2.*, p3.*, p4.* from p1, p2, p3, p4;
insert or replace into p1 values (1, 'patata');
select p1.*, p2.*, p3.*, p4.* from p1, p2, p3, p4;
insert or ignore into p1 values (1, 'krieger');
select p1.*, p2.*, p3.*, p4.* from p1, p2, p3, p4;
is, despite the different conflict resolution strategies in each trigger:
Parse error near line 9: near "or": syntax error
  create trigger trg_p4_1 after insert or replace on p1 for each row begin inser
                         error here ---^
+----+-------+----+------------+----+------------+----+------------+
| id |  col  | id |    col     | id |    col     | id |    col     |
+----+-------+----+------------+----+------------+----+------------+
| 1  | valor | 1  | valor otro | 1  | valor ohno | 1  | valor iffy |
+----+-------+----+------------+----+------------+----+------------+
+----+--------+----+-------------+----+-------------+----+-------------+
| id |  col   | id |     col     | id |     col     | id |     col     |
+----+--------+----+-------------+----+-------------+----+-------------+
| 1  | patata | 1  | patata otro | 1  | patata ohno | 1  | patata iffy |
+----+--------+----+-------------+----+-------------+----+-------------+
+----+--------+----+-------------+----+-------------+----+-------------+
| id |  col   | id |     col     | id |     col     | id |     col     |
+----+--------+----+-------------+----+-------------+----+-------------+
| 1  | patata | 1  | patata otro | 1  | patata ohno | 1  | patata iffy |
+----+--------+----+-------------+----+-------------+----+-------------+

(5) By anonymous on 2025-01-13 18:32:23 in reply to 4.1 [source]

Ok, understood.

Thank you.