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
