Good point. However, it looks to me that if you replace ignore with fail (and
a message) you may get the semantics you want. Here's a little test script:
drop table if exists iupdate;
create table if not exists iupdate (c1 integer primary key,tag text);
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
insert into iupdate (tag) values ('initial');
select * from iupdate;
create trigger if not exists iupdate_up before update on iupdate
begin
select raise(fail,'halt in processing requested') where old.c1 = 7;
end;
begin;
update iupdate set tag = 'updated';
select * from iupdate;
end;
Peter
From: jhnlmn <jhn...@yahoo.com>
>To: sqlite-users@sqlite.org
>Sent: Monday, June 24, 2013 11:02 AM
>Subject: Re: [sqlite] How to interrupt a long running update without roll back?
>
>
>Peter Aronson <pbaronson@...> writes:
>> create trigger inter_update before update on my_table
>> begin
>> select raise(ignore) where my_function() = 1;
>> end;
>
>No, this is the worst approach so far.
>This "raise(ignore)" does abort that single update
>of that particular row, but the loop continues.
>So, if I called
>UPDATE T set C1 = calculation(C2) where C1 is NULL
>on 100,000 rows and my_function will begin returning 1 after 10,000 calls,
>then my_function will be called 100,000 times and calculation will be called
>100,000 times, but only 10,000 rows will be actually updated.
>And the time of such semi-aborted update is almost the same
>as time of a single update without any triggers
>(almost 5 seconds, which is close to the timeout time).
>But then I will have to call update again, this time it will make 90,000
>calls, etc, etc, etc.
>The total time is 6 times worse than time of running single update query
>without any limits.
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
