'Why does constraint fail when upsert used in conjunction with triggers?

Please could someone explain why this unique constraint fails and if there is any workaround or suggestions.

With this schema:

CREATE TABLE abc 
(
    a INTEGER NOT NULL,
    b INTEGER NOT NULL,
    c INTEGER NOT NULL
);

CREATE UNIQUE INDEX unique_a_b ON abc(a, b);

CREATE TABLE xyz 
(
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    z INTEGER NOT NULL
);

CREATE UNIQUE INDEX unique_x_y ON xyz(x, y);

I have triggers on a table xyz for both insert and update operations, they both perform insert or replace on table abc which has a unique index.

CREATE TRIGGER on_insert_xyz
AFTER INSERT 
ON xyz
BEGIN
    INSERT OR REPLACE INTO abc(a, b, c)
    SELECT * FROM xyz LIMIT 1;
END;

CREATE TRIGGER on_update_xyz
AFTER UPDATE 
ON xyz
BEGIN
    INSERT OR REPLACE INTO abc(a, b, c)
    SELECT * FROM xyz LIMIT 1;
END;

I have an upsert statement on table xyzthat causes the triggers to execute. If the upsert statement causes the update trigger to execute, then the unique index constraint fails on table abc.

INSERT INTO xyz(x, y, z) VALUES(1, 2, 3) ON CONFLICT(x, y) DO UPDATE SET z = excluded.z -- Works;
INSERT INTO xyz(x, y, z) VALUES(1, 2, 4) ON CONFLICT(x, y) DO UPDATE SET z = excluded.z -- Fails;

UNIQUE constraint failed: abc.a, abc.b



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source