A quick test shows that, indeed, an INSERT OR IGNORE inside a trigger that fails will not be ignored if the top-level statement has an OR ABORT (or rollback, or fail) clause.
I can't imagine why one would ever want such behavior, but since I can live with it (as shown below), I'll just conclude that my imagination fails me. (Well, if one did want to condition on conflict handling in a trigger according to top-level on conflict handling, what SQLite3 does now certainly works, though a function that returns the top-level on conflict handling would allow for more flexibility.) sqlite> create table t(a unique); sqlite> create view v as select a from t; sqlite> create trigger vi instead of insert on v for each row begin insert or ignore into t (a) values (NEW.a); end; sqlite> insert or abort into v (a) values (1); sqlite> insert or abort into v (a) values (1); Error: column a is not unique sqlite> insert or fail into v (a) values (1); Error: column a is not unique sqlite> insert or rollback into v (a) values (1); Error: column a is not unique sqlite> sqlite> drop trigger vi; sqlite> create trigger vi instead of insert on v for each row begin insert into t (a) select NEW.a where not exists (select a from t); end; sqlite> insert or abort into v (a) values (1); sqlite> Fortunately a top-level OR ABORT does not override a RAISE(IGNORE) in a trigger: sqlite> drop trigger vi; sqlite> create trigger vi instead of insert on v for each row begin select raise(ignore) where exists (select a from t); insert into t (a) values (new.a); end; sqlite> insert or abort into v (a) values (1); sqlite> It'd have been disastrous, IMO, if top-level on conflict clauses could override RAISE(IGNORE)s. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
