Hello mailing list, happy new year! I was wondering if anyone can help
explain why this is an error:
---
sqlite> pragma foreign_keys = on;
sqlite> drop table if exists child;
sqlite> drop table if exists parent;
sqlite> drop table if exists changelog;
sqlite> create table parent (
...> parent_id text not null primary key,
...> parent_name text
...> );
sqlite> create table child (
...> child_id text not null primary key,
...> parent_id text not null references parent on update cascade,
...> child_name text
...> );
sqlite> create table changelog (
...> change_id integer not null primary key autoincrement,
...> tbl text not null,
...> id text not null,
...> unique (tbl, id)
...> );
sqlite> DROP TRIGGER IF EXISTS child_inserts;
sqlite> CREATE TRIGGER child_inserts AFTER INSERT ON child
...> BEGIN
...> INSERT OR REPLACE INTO changelog (tbl, id) VALUES ('child',
NEW.child_id);
...> END;
sqlite> DROP TRIGGER IF EXISTS child_updates;
sqlite> CREATE TRIGGER child_updates AFTER UPDATE ON child
...> BEGIN
...> INSERT OR REPLACE INTO changelog (tbl, id) VALUES ('child',
NEW.child_id);
...> END;
sqlite> insert into parent (parent_id, parent_name) VALUES
('some_parent_uuid', 'the parent');
sqlite> insert into child (child_id, parent_id, child_name) VALUES
('some_child_uuid', 'some_parent_uuid', 'the child');
sqlite> update parent set parent_id = 'updated' where parent_id =
'some_parent_uuid';
Error: UNIQUE constraint failed: changelog.tbl, changelog.id
---
The trigger documentation 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."
I tried adding an "OR REPLACE" to the update statement but that doesn't fix
the issue.
sqlite> update or replace parent set parent_id = 'updated' where parent_id
= 'some_parent_uuid';
Error: UNIQUE constraint failed: changelog.tbl, changelog.id
Is this because the "ON UPDATE CASCADE" action is considered to be the
statement causing the trigger to fire, meaning the default conflict
handling policy of ABORT is used? If so, is there any way to write
something like "ON UPDATE CASCADE OR REPLACE"? My current workaround is to
just manually delete from changelog in the child_updates trigger instead of
relying on INSERT OR REPLACE to do it.
Any insight would be much appreciated!
Thanks,
Mike
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
