Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > Hello, > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > Is there a pragma to disable triggers on the sqlite database? > Regards, > Marton I think a better solution here would be some kind of syntax that lets you perform multiple data-manipulation operations "simultaneously"; for example: INSERT INTO DEBITS (...) VALUES (...), INSERT INTO CREDITS (...) VALUES (...); In this example, the 2 statements are separated by a comma rather than a semicolon (you can use different syntax instead if you want), so they are treated as a single statement in that their updates all happen as a single update. In this case, the triggers don't have to be disabled; they would just run after the combined statement completes. Note that my proposal is not the same as transactions, since you can have triggers run between statements in a transaction and said statements are not collectively atomic in the same way where no database state exists between statements. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > now this raises questions: > > eg: > CREATE TABLE clients ... > CREATE VIEW getclients AS select * from clients. > > now if I update the 'getlist' view that will with a instead of trigger > update the clients table > > that will raise the triggers for the client table, I am right? > Yes, it will. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
now this raises questions: eg: CREATE TABLE clients ... CREATE VIEW getclients AS select * from clients. now if I update the 'getlist' view that will with a instead of trigger update the clients table that will raise the triggers for the client table, I am right? - Original Message - From: "Kees Nuyt" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Thursday, August 14, 2008 8:59 PM Subject: Re: [sqlite] is there a pragma to disable triggers? > On Mon, 11 Aug 2008 13:16:45 +0300, you wrote: > >>Hello, >> >>I am working on a syncing project and I do have a lot of triggers to >>read/write foreign keys. >>And while I do the sync of one table the triggers are causing a strange >>effect (as the other table is not yet synced). >>Is there a pragma to disable triggers on the sqlite database? > > Perhaps you can solve the problem with "INSTEAD OF" triggers > on one or more views. Yes, that's possible: a view is > `virtually` updatable when you define triggers for all > statements that will be used to update the view. > It is a very powerful mechanism. > > The updatable view can be (almost) any select or join and > has to contain all columns of all tables that you need to > update with the INSERT | UPDATE view_name statements. All > foreign key contraints would be handled by the INSTEAD OF > triggers if all your updates are performed on those views > instead of tables. > > sql-statement ::= > CREATE [TEMP | TEMPORARY] TRIGGER > [IF NOT EXISTS] trigger-name > INSTEAD OF database-event > ON [database-name .] view-name > trigger-action > > database-event ::= > DELETE | INSERT | UPDATE | UPDATE OF > column-list > > http://www.sqlite.org/lang_createtrigger.html > >>Regards, >>Marton > > I hope this helps. > -- > ( Kees Nuyt > ) > c[_] > ___ > 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
Re: [sqlite] is there a pragma to disable triggers?
On Mon, 11 Aug 2008 13:16:45 +0300, you wrote: >Hello, > >I am working on a syncing project and I do have a lot of triggers to >read/write foreign keys. >And while I do the sync of one table the triggers are causing a strange effect >(as the other table is not yet synced). >Is there a pragma to disable triggers on the sqlite database? Perhaps you can solve the problem with "INSTEAD OF" triggers on one or more views. Yes, that's possible: a view is `virtually` updatable when you define triggers for all statements that will be used to update the view. It is a very powerful mechanism. The updatable view can be (almost) any select or join and has to contain all columns of all tables that you need to update with the INSERT | UPDATE view_name statements. All foreign key contraints would be handled by the INSTEAD OF triggers if all your updates are performed on those views instead of tables. sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name INSTEAD OF database-event ON [database-name .] view-name trigger-action database-event ::= DELETE | INSERT | UPDATE | UPDATE OF column-list http://www.sqlite.org/lang_createtrigger.html >Regards, >Marton I hope this helps. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Regarding: I can't use command line utility as I do this on a smartphone device. and I am using sqlite by c# the Ado .Net library which is very new to me and I can for now only execute insert,delete,update and basic commandtext queries. -- Well, you might have to resort to a more complex method. The following queries may be of help -- they might be part of general "DropAllTriggers" and a "RecreateAllTriggers" subroutines. -- Build SQL to re-create all triggers select sql as BuildSQL from sqlite_master where type= 'trigger'; -- Build SQL to drop all triggers in current database select 'DROP TRIGGER ' || name || ';' as DropSQL from sqlite_master where type= 'trigger'; You may even find that ability to read sql from a file is so useful that you want to implement it within your ADO application. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Regarding: Is there a pragma to disable triggers on the sqlite database? Hello Marton, There's no such pragma to my knowledge. See http://www.sqlite.org/pragma.html Perhaps you can use the command line utility "sqlite3" to ".dump" the schema of your database, isolate just the CREATE TRIGGER commands, and call this file "addTriggers.sql" Then grep that file for just the CREATE TRIGGER first lines, and edit these down to a "dropTriggers.sql" file. Now the sqlite3 ".read" command will allow you to quickly delete or restore triggers with a simple batch file. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
On 8/14/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > Kodok Márton wrote: > > > > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > > Is there a pragma to disable triggers on the sqlite database? > > > > > No, there is no way to disable triggers. > > You could save the SQL used to create the triggers from the > sqlite_master table, drop the triggers, do the updates, and then finally > use the saved SQL to recreate the triggers after the updates. > > HTH > > Dennis Cote > Interestingly, I have found need for such a mechanism as well... esp. while updating a table but wishing not to update the TRIGGERed FTS tables. Ended up following the approach suggested above, but would have been nice to have something like WITHOUT TRIGGERS kinda mechanism. Or a PRAGMA DISABLE_TRIGGERS PRAGMA ENABLE_TRIGGERS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > Is there a pragma to disable triggers on the sqlite database? > No, there is no way to disable triggers. You could save the SQL used to create the triggers from the sqlite_master table, drop the triggers, do the updates, and then finally use the saved SQL to recreate the triggers after the updates. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Hello, I am working on a syncing project and I do have a lot of triggers to read/write foreign keys. And while I do the sync of one table the triggers are causing a strange effect (as the other table is not yet synced). Is there a pragma to disable triggers on the sqlite database? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is there a pragma to disable triggers?
Hello, I am working on a syncing project and I do have a lot of triggers to read/write foreign keys. And while I do the sync of one table the triggers are causing a strange effect (as the other table is not yet synced). Is there a pragma to disable triggers on the sqlite database? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
