D. Richard Hipp wrote:
> On Jun 24, 2009, at 9:17 AM, Michal Seliga wrote:
>
>> hi
>>
>> in my application i sometimes must insert huge amount of pre-
>> prepared data so i
>> don't want triggers to do any action while i am inserting them
>>
>> for this reason i created one small table which is normally empty,
>> however if it
>> contains record set to 1 triggers shoudl not do any action. to make
>> this happen
>> i use triggers like this following example
>>
>> CREATE TRIGGER sync_Activity_oninsert after insert on Activity
>> for each row
>> when (not exists(select * from sync_block where sub_block=1))
>> begin
>> --action
>> end;
>>
>> to block triggers i do: insert into sync_block values(1);
>> to enable them back i do: delete from sync_block;
>>
>>
>> this helped to stop triggers from doing any action, but they still
>> launch and in
>> result import of data takes 2x more with triggers then without them.
>>
>> is there any other, faster, way to temporarily disable triggers?
>>
>> i found one more possibility, someone recommended it on this list in
>> year 2005
>> that its possible to list all create trigger statements from
>> database, drop
>> them, make action and recreate them back. this will work, but time
>> for creating
>> so many triggers (yes, they are many) may be also very bad. in
>> addition it
>> doesn't seem as very nice solution to me...
>>
>> the most prefered way for me would be to use some pragma to disable
>> triggers,
>> but looking to documentation i wasn't able to find any...
>>
>> maybe someone has any other idea?
>
> Instead of
>
> WHEN not exists(SELECT * FROM ....)
>
> create an application-defined function (perhaps called
> "enable_triggers()") that returns either 1 or 0 depending on the
> setting of some variable in your application. Then use
>
> WHEN enable_triggers()
>
> The triggers will still launch, but calling an application-defined
> function takes much less time than running NOT EXISTS, so the triggers
> will very quickly figure out that they should exit without doing
> anything.
>
>
strangely, it didn't helped, time is almost the same with not exists or with
your idea. actually with function its even a little bit worse
what i did was that i created custom function using:
sqlite3_create_function(handle,"enable_triggers",-1,SQLITE_ANY,NULL,enable_triggers,NULL,NULL)
and function enable_triggers is like this:
static int mEnableTriggers;
static void enable_triggers(sqlite3_context *context, int nieco, sqlite3_value
**value)
{
sqlite3_result_int(context,mEnableTriggers);
}
and global variable mEnableTriggers is set to 1 or to 0 depending on what i want
it do do.
yet sample test showed almost the same times as it was, with some little sample
data reults were:
without triggers: 3.766 seconds
with triggers which use not exists: 5.438 seconds
with triggers which use function: 5.484 seconds
this was time needed to insert ~25000 records to various tables so you can
imagine how it would be with more....
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> _______________________________________________
> 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
