Yes a trigger is right thing to use. I started with a trigger and moved to a check purely for performance. The trouble being that inserts are done in blocks of anywhere up to 10,000. The trigger overhead on the big inserts is significant (I guess I should re-check to see if that has changed since 3.7) The check version handles 10,000 inserts in a timeframe that is not worth measuring. Instant from a user perspective. I have vague memories when I first did this a(a long time ago) of measuring the overhead of a trigger that did nothing. I should probably do this again. The trigger version had the same tests as the check. See below.
----- I have sanitized this (a little) CHECKS check( instr('ABCDEFGH',substr(col1,10,1)) ), check( col2 GLOB '123 45[67]000[0-9][0-9][0-9][0-9][0-9][0-9]' ), check( CASE substr(col2,7,1) WHEN '6' THEN instr('XY',substr(col1,10,1)) WHEN '7' THEN instr('VW',substr(col1,10,1)) END ) TRIGGER DROP TRIGGER IF EXISTS tab; CREATE TRIGGER validate_tab BEFORE INSERT ON tab FOR EACH ROW BEGIN CASE WHEN instr('ABCDEFGH',substr(NEW.col1,10,1)) = 0 THEN RAISE ( ABORT, 'Invalid col1' ) WHEN NEW.col2 NOT GLOB '123 45[67]000[0-9][0-9][0-9][0-9][0-9][0-9]' THEN RAISE ( ABORT, 'Invalid col2' ) WHEN CASE "ditto for third check" END = 0 THEN RAISE ( ABORT, 'Invalid col1 or col2' ) END; END; On Mon, Apr 3, 2017 at 10:23 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 Apr 2017, at 12:41am, David Burgess <dburg...@gmail.com> wrote: > >> Bad trigger performance prevents me using your suggested solution. > > I’d like to see your TRIGGER. But if a well-written TRIGGER can’t reach the > performance you need (it’ll never be as fast as a constraint) then your best > solution is to do sanity-checking in your programming language. > >> Is there a secret to making triggers perform like CHECK? > > No. They do two different jobs. CHECK is to ensure integrity of the TABLE. > A constraint applies to all the rows in the table all the time. The thing > you want does not involve CHECK since existing entries in the database which > violate your rule are not a problem. > > TRIGGERs apply when things are added to, modified, or deleted from the TABLE. > But a TRIGGER can be used to check /new/ entries in the database. It would > seem that a TRIGGER might be useful to you. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- David Burgess _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users