On 30 Dec 2012, at 9:52am, Igor Korot <ikoro...@gmail.com> wrote: > I want to create a trigger on the table. The trigger will be fired > before insertion. > In that trigger I want to check if the data is already present. > > I have: > > CREATE TABLE test( id integere primary key, name varchar(70));
By the way, SQLite has no varchar, just TEXT. > 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? I would not use a trigger. SQLite has the ability to prevent this all by itself. You can do it two ways: one by putting it in the TABLE definition: CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT UNIQUE ON CONFLICT FAIL) You might prefer ON CONFLICT IGNORE which would not generate an error message, if you felt it was good enough that the data was already in the table. The second way is by creating a special index: CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT); CREATE UNIQUE INDEX testNameUnique ON test (name) There is no real difference in time or efficiency in which one you choose. It's about whether you think of duplication as a fault in the TABLE or something which might one day not be a problem. By the way, had you considered this: INSERT INTO test( NULL, "abc def" ); INSERT INTO test( NULL, "abc DEF" ); by default SQLite uses NOCASE, in other words, it will treat these two strings as the same, and the second INSERT would not be allowed if you used one of the above. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
