Igor Korot <ikoro...@gmail.com> wrote: > I have: > > CREATE TABLE test( id integere primary key, name varchar(70)); > INSERT INTO test( NULL, "abc def" ); > INSERT INTO test( NULL, "def abc" ); > > Can I prevent the second insertion with the trigger? > Or I should make some app logic to prevent that?
Preventing insertion (with a trigger, or by other means) is easy. The hard part is figuring out that 'def abc' is in fact a "duplicate" of 'abc def'; I assume that you want something general like "two strings consist of the same set of words, regardless of order". I can't think of any way to express such a condition in pure SQL and built-in functions - you would need a custom function or a custom collation: http://sqlite.org/c3ref/create_function.html http://sqlite.org/c3ref/create_collation.html If you go the collation route, then you wouldn't need a trigger - just create a unique index with this collation: create unique index MyIndex on test(name collate MyCustomCollation); If the actual condition you want to enforce is simpler than the one I assumed, then there might be ways that don't require custom coding. For example, if the values in test.name are always presumed to consist of two words separated by a space, then you can do something like this: create trigger EnforceUnique before insert on test begin select raise(fail) from test where name in (new.name, substr(new.name, instr(new.name, ' ') + 1) || ' ' || substr(new.name, 1, instr(new.name, ' ') - 1)); end; Though in this case, I'd personally have two separate columns instead, say firstName and lastName. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
