Re: [sqlite] Triggers for generated columns?

2019-12-02 Thread Stephen F. Booth
Hi Keith, Thanks for the reply. On Mon, Dec 2, 2019 at 6:31 PM Keith Medcalf wrote: > Well, of course the trigger did not fire. You created an AFTER UPDATE > trigger, but only did an insert. So of course one would not expect the > trigger to fire. You're right- this was a copy/paste error i

Re: [sqlite] Triggers for generated columns?

2019-12-02 Thread Keith Medcalf
On Monday, 2 December, 2019 17:10, Stephen F. Booth wrote: >I have been trying out generated column support in the 3.31.0 prerelease. >Thank you for adding such a useful feature! >When I create a trigger for an update of a generated column the trigger >is successfully created but it never fires

[sqlite] Triggers for generated columns?

2019-12-02 Thread Stephen F. Booth
I have been trying out generated column support in the 3.31.0 prerelease. Thank you for adding such a useful feature! When I create a trigger for an update of a generated column the trigger is successfully created but it never fires. I could not find any mention in the draft documentation of gener

Re: [sqlite] Triggers and CTE's

2014-11-12 Thread James K. Lowden
On Tue, 11 Nov 2014 17:15:53 -0600 Ben Newberg wrote: > CREATE TRIGGER t_populate_zweeks > AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 > BEGIN > DELETE FROM zWeeks; > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from > Weeks limit 10) > INSERT INTO zWeeks (Week)

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Richard Hipp
On Tue, Nov 11, 2014 at 9:01 PM, Igor Tandetnik wrote: > On 11/11/2014 8:37 PM, Richard Hipp wrote: > >> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik >> wrote: >> >> On 11/11/2014 6:15 PM, Ben Newberg wrote: >> >>> >>> Looks like a bug to me. The statement works standalone, but not within a >

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Igor Tandetnik
On 11/11/2014 8:37 PM, Richard Hipp wrote: On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: Looks like a bug to me. The statement works standalone, but not within a trigger. There are many limitations and restrictions on the statements inside

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
Thanks everyone. I will have the programming language do the work on this one instead of going the trigger route. On Nov 11, 2014 7:39 PM, "Richard Hipp" wrote: > On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik > wrote: > > On 11/11/2014 6:15 PM, Ben Newberg wrote: > > > > Looks like a bug to me

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Richard Hipp
On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: > > Looks like a bug to me. The statement works standalone, but not within a > trigger. > There are many limitations and restrictions on the statements inside of triggers. See the trigger documentat

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Simon Slavin
On 11 Nov 2014, at 11:59pm, Ben Newberg wrote: > The 10 is just an arbitrary value I chose for this example. The user > actually determines the value at run-time, so this value could be any > integer. I have a way to settle that, if only I could figure out how I can > get this trigger working.

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Igor Tandetnik
On 11/11/2014 6:15 PM, Ben Newberg wrote: CREATE TRIGGER t_populate_zweeks AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 BEGIN DELETE FROM zWeeks; WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks limit 10) INSERT INTO zWeeks (Week) select wk from Weeks; END;

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
Thanks Simon. The 10 is just an arbitrary value I chose for this example. The user actually determines the value at run-time, so this value could be any integer. I have a way to settle that, if only I could figure out how I can get this trigger working. BEN On Tue, Nov 11, 2014 at 5:41 PM, Simon

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Simon Slavin
On 11 Nov 2014, at 11:15pm, Ben Newberg wrote: > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks > limit 10) > INSERT INTO zWeeks (Week) select wk from Weeks; Just use 10 INSERT commands. I don't know what's causing your error message, but your code will be simpler if

[sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
All, Is it possible to have CTE's within triggers? The way I read the 'SQL As Understood By SQLite', one can, but I could be misinterpreting it. My DDL for my trigger is as follows: CREATE TRIGGER t_populate_zweeks AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 BEGIN DELETE FROM zW

Re: [sqlite] Triggers to enforce table permissions

2013-12-07 Thread Joshua Grauman
Perfect! Always new nuggets of goodness discovered in sqlite. Thanks! Josh On Sat, Dec 7, 2013 at 1:45 PM, Joshua Grauman wrote: Is there a way to prevent changes to the schema, while still allowing inserts of new data to existing tables? The sqlite3_set_authorizer() interface ( http://w

Re: [sqlite] Triggers to enforce table permissions

2013-12-07 Thread Richard Hipp
On Sat, Dec 7, 2013 at 1:45 PM, Joshua Grauman wrote: > > Is there a way to prevent changes to the schema, while still allowing > inserts of new data to existing tables? > The sqlite3_set_authorizer() interface ( http://www.sqlite.org/c3ref/set_authorizer.html) can register a callback that preve

Re: [sqlite] Triggers to enforce table permissions

2013-12-07 Thread Constantine Yannakopoulos
On Sat, Dec 7, 2013 at 8:45 PM, Joshua Grauman wrote: > Ok, so now I have a new question. > > Is there a way to prevent changes to the schema, while still allowing > inserts of new data to existing tables? I'd love to just prevent any > changes to sqlite_master for a given database connection. I

Re: [sqlite] Triggers to enforce table permissions

2013-12-07 Thread Joshua Grauman
Ok, so now I have a new question. Is there a way to prevent changes to the schema, while still allowing inserts of new data to existing tables? I'd love to just prevent any changes to sqlite_master for a given database connection. I tried adding triggers to it similar to the ones I used below,

Re: [sqlite] Triggers to enforce table permissions

2013-12-06 Thread Joshua Grauman
Nevermind, I found the recursive_triggers PRAGMA... Thanks! Josh Hello all, I'm trying to create simple permissions for a table to prevent unwanted modification of certain table rows. I'm brand new to Triggers, but I almost have what I want working. Imagine I have a table t1 with some data,

[sqlite] Triggers to enforce table permissions

2013-12-06 Thread Joshua Grauman
Hello all, I'm trying to create simple permissions for a table to prevent unwanted modification of certain table rows. I'm brand new to Triggers, but I almost have what I want working. Imagine I have a table t1 with some data, and a permission integer. I have two triggers that prevent modifica

Re: [sqlite] A question about the ancient history of SQLite triggers

2013-03-06 Thread Philip Warner
On 6/03/2013 1:59 AM, Jay A. Kreibich wrote: > In this case, it is any trigger that invokes any other trigger. > Prior to 3.6.18 there was no trigger "stack" and triggers could be > only one layer deep. Ah, thanks. That solves the problem. I can dynamically generate a single inefficient trigg

Re: [sqlite] A question about the ancient history of SQLite triggers

2013-03-05 Thread Dan Kennedy
On 03/05/2013 09:59 PM, Jay A. Kreibich wrote: On Tue, Mar 05, 2013 at 11:20:27PM +1100, Philip Warner scratched on the wall: On 5/03/2013 9:53 PM, Richard Hipp wrote: Recursive triggers (triggers that invoke themselves either directly or indirectly) were added in version 3.6.18, 2009-09-11.

Re: [sqlite] A question about the ancient history of SQLite triggers

2013-03-05 Thread Jay A. Kreibich
On Tue, Mar 05, 2013 at 11:20:27PM +1100, Philip Warner scratched on the wall: > On 5/03/2013 9:53 PM, Richard Hipp wrote: > > Recursive triggers (triggers that invoke themselves either directly or > > indirectly) were added in version 3.6.18, 2009-09-11. > > These are not strictly recursive; the

Re: [sqlite] A question about the ancient history of SQLite triggers

2013-03-05 Thread Philip Warner
On 5/03/2013 9:53 PM, Richard Hipp wrote: > Recursive triggers (triggers that invoke themselves either directly or > indirectly) were added in version 3.6.18, 2009-09-11. These are not strictly recursive; the 'when' clause means that trigger 1 will cause trigger 2 to be called etc. _

Re: [sqlite] A question about the ancient history of SQLite triggers

2013-03-05 Thread Richard Hipp
On Tue, Mar 5, 2013 at 1:11 AM, Philip Warner wrote: > > What I am seeing in 3.5.9 on Android is that the triggers are executed > precisely once each, rather than once for each row. > Recursive triggers (triggers that invoke themselves either directly or indirectly) were added in version 3.6.18,

[sqlite] A question about the ancient history of SQLite triggers

2013-03-04 Thread Philip Warner
This is a question about triggers in version 3.5.9...if anyone remembers it. The reason I am asking is that I want to support Android 2.1, which has 3.5.9 installed by default. I have a set of triggers on a table: tg1 before insert on sometable when new.f1 = 3 insert into sometable(f1, ...)

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Jay A. Kreibich
On Fri, Jun 15, 2012 at 12:42:45PM +, Black, Michael (IS) scratched on the wall: > But as somebody pointed out if you muck with database from the shell or > any other program it will die with "function not found" if the trigger > is called. > > Ergo a dummy function to make it happy and non-f

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Igor Tandetnik
Black, Michael (IS) wrote: > Looks like that is getting close...but when is this called? BEFORE or AFTER? After, as far as I can tell. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/list

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Black, Michael (IS)
Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Friday, June 15, 2012 8:15 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Tri

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Igor Tandetnik
Bageesh.M.Bose wrote: > Can anyone tell "How to use Triggers in Sqlite using C" What do you mean by "use triggers", exactly? What are you trying to achieve? If you are asking how to create a trigger, then it's the same way as creating a table - you open a database connection (with sqlite3_open

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Igor Tandetnik
Black, Michael (IS) wrote: > Could the SQLite code add a new dummy function for a callback which we can > then override with our own library? sqlite3_update_hook -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.or

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Black, Michael (IS)
se Subject: EXT :Re: [sqlite] Triggers in Sqlite using C On Fri, Jun 15, 2012 at 12:12:22PM +, Black, Michael (IS) scratched on the wall: > Could the SQLite code add a new dummy function for a callback which > we can then override with our own library? Just use an SQL function: h

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Jay A. Kreibich
On Fri, Jun 15, 2012 at 12:12:22PM +, Black, Michael (IS) scratched on the wall: > Could the SQLite code add a new dummy function for a callback which > we can then override with our own library? Just use an SQL function: http://sqlite.org/c3ref/create_function.html -j -- Jay A. Kr

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Black, Michael (IS)
atabase Subject: EXT :Re: [sqlite] Triggers in Sqlite using C On Fri, Jun 15, 2012 at 11:59:33AM +0530, Bageesh.M.Bose wrote: > Can anyone tell "How to use Triggers in Sqlite using C" > Triggers in SQLite can only contain SQL DML statements. But, you can interface with C by definin

Re: [sqlite] Triggers in Sqlite using C

2012-06-15 Thread Christian Smith
On Fri, Jun 15, 2012 at 11:59:33AM +0530, Bageesh.M.Bose wrote: > Can anyone tell "How to use Triggers in Sqlite using C" > Triggers in SQLite can only contain SQL DML statements. But, you can interface with C by defining functions, then calling those functions using SQL SELECT command. For exam

[sqlite] Triggers in Sqlite using C

2012-06-14 Thread Bageesh.M.Bose
Can anyone tell "How to use Triggers in Sqlite using C" -- With Regards, *Bageesh.M.Bose* ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] triggers : NEW keyword with multiple tables

2011-10-25 Thread Sébastien Escudier
> You haven't given the view explicit column names How ? > try this instead: > > CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type > as table2_type FROM Yes this works on latest version. But I wonder why the other syntax is not accepted anymore. Because I'll have to rewrite m

Re: [sqlite] triggers : NEW keyword with multiple tables

2011-10-25 Thread Sébastien Escudier
> Try NEW."table1.type" and NEW."table2.type" This works with my old 3.6 version but not on 3.7.8 I still get : Error: no such column: NEW.table1.type ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] triggers : NEW keyword with multiple tables

2011-10-25 Thread Igor Tandetnik
On 10/25/2011 10:59 AM, Sébastien Escudier wrote: Hello, I used to do something like this on older sqlite versions : (this does not really makes sense here, but this is a simplified) CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Re: [sqlite] triggers : NEW keyword with multiple tables

2011-10-25 Thread Doug Currie
On Oct 25, 2011, at 10:59 AM, Sébastien Escudier wrote: > CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view > BEGIN > INSERT INTO table1(type) VALUES(NEW.table1.type); > INSERT INTO table2(type) VALUES(NEW.table2.type); > END; > > ... > > Why this syntax does not work anymore ? You haven'

[sqlite] triggers : NEW keyword with multiple tables

2011-10-25 Thread Sébastien Escudier
Hello, I used to do something like this on older sqlite versions : (this does not really makes sense here, but this is a simplified) CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER JOIN table2 ON table1.id = table2.id; CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_vi

Re: [sqlite] triggers & last_insert_rowid?

2011-10-21 Thread James Hartley
On Fri, Oct 21, 2011 at 12:31 PM, Pavel Ivanov wrote: > http://www.sqlite.org/c3ref/last_insert_rowid.html > > "If an INSERT occurs within a trigger or within a virtual table > method, then this routine will return the rowid of the inserted row as > long as the trigger or virtual table method is

Re: [sqlite] triggers & last_insert_rowid?

2011-10-21 Thread Pavel Ivanov
> Because of the trigger's insertion into the second table, should not the > second select statement return a value of 2 instead of 1 since the last > insertion occurred in table t_log? http://www.sqlite.org/c3ref/last_insert_rowid.html "If an INSERT occurs within a trigger or within a virtual ta

[sqlite] triggers & last_insert_rowid?

2011-10-21 Thread James Hartley
After implementing simple auditing of table changes, last_insert_rowid() still provides the value I really want even though additional table inserts are being performed in the background. To illustrate: CREATE TABLE t (a); CREATE TABLE t_log ( timestamp DATETIME NULL DEFAULT CURRENT_TIMESTAMP

Re: [sqlite] Triggers

2010-09-29 Thread Simon Davies
On 29 September 2010 11:49, wrote: > Hi, > > I have this table: > > CREATE TABLE [repo] ( >     [code] varchar(20) PRIMARY KEY NOT NULL, >     [klass] varchar(2) > ); > > and trying to create this trigger: > > CREATE TRIGGER repo_klass > BEFORE INSERT ON `repo` > BEGIN >        new.klass = SUBSTR

[sqlite] Triggers

2010-09-29 Thread db . subscriptions
Hi, I have this table: CREATE TABLE [repo] ( [code] varchar(20) PRIMARY KEY NOT NULL, [klass] varchar(2) ); and trying to create this trigger: CREATE TRIGGER repo_klass BEFORE INSERT ON `repo` BEGIN new.klass = SUBSTR(new.code,4,2); END; I got this error: near "new": syntax e

[sqlite] triggers revisited

2009-11-03 Thread Victor Mayevski
Ok, I will rephrase my question although it appears that the answer is has been already preconceived. I am writing an abstraction layer to Sqlite where tables, views, triggers etc are presented as XOTcl Classes/Objects. If two different scripts access the database and one of them creates a new tabl

Re: [sqlite] triggers revisited

2009-11-03 Thread P Kishor
On Tue, Nov 3, 2009 at 1:23 PM, Victor Mayevski wrote: > Ok, I will rephrase my question although it appears that the answer is > has been already preconceived. I am writing an abstraction layer to > Sqlite where tables, views, triggers etc are presented as XOTcl > Classes/Objects. If two differen

Re: [sqlite] triggers revisited

2009-11-03 Thread Simon Slavin
On 3 Nov 2009, at 7:23pm, Victor Mayevski wrote: > Ok, I will rephrase my question although it appears that the answer is > has been already preconceived. I am writing an abstraction layer to > Sqlite where tables, views, triggers etc are presented as XOTcl > Classes/Objects. If two different scr

Re: [sqlite] triggers revisited

2009-11-03 Thread Igor Tandetnik
Victor Mayevski wrote: > Ok, I will rephrase my question although it appears that the answer is > has been already preconceived. I am writing an abstraction layer to > Sqlite where tables, views, triggers etc are presented as XOTcl > Classes/Objects. If two different scripts access the database an

Re: [sqlite] triggers question, OLD reference is not available after SET statment

2009-08-27 Thread Simon Davies
2009/8/27 Jarod Tang : > Hi List users, > > In my code, i create two triggers (Trigger_A, Trigger_B) on a table ( > sample_db), but after execution, it's found that Trigger_A is not executed > correctly ( old.msg_box hasnt be put into table log ). And it seems to me > that SqlLite invalids the OLD

[sqlite] triggers question, OLD reference is not available after SET statment

2009-08-27 Thread Jarod Tang
Hi List users, In my code, i create two triggers (Trigger_A, Trigger_B) on a table ( sample_db), but after execution, it's found that Trigger_A is not executed correctly ( old.msg_box hasnt be put into table log ). And it seems to me that SqlLite invalids the OLD reference after it found it be ref

[sqlite] Triggers on attached databases

2008-09-18 Thread Dennis Volodomanov
Hello all, Is it possible to create a trigger on an attached database? I can't seem to, so I was wondering whether my trigger SQL is wrong (although it works if I remove the attached database's name from the table). The only other way that I see to do what I need would be to create temporary t

[sqlite] Triggers on attached databases

2008-09-18 Thread Dennis Volodomanov
Hello all, Is it possible to create a trigger on an attached database? I can't seem to, so I was wondering whether my trigger SQL is wrong (although it works if I remove the attached database's name from the table). The only other way that I see to do what I need would be to create temporary tabl

[sqlite] SQLite triggers messages

2008-07-13 Thread Alexey Pechnikov
Hello! I try this code create trigger view_objects_update before update on view_objects begin select RAISE (ABORT,'Row id=' || NEW.id); end; but get "SQL error: near "||": syntax error". How can I do it? Best regards, Alexey. ___ sqlite-users maili

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Scott Hess
mine when to check for data > > changes. Of course, this won't tell you _what_ changed, which might > > not help if you're only watching for a tiny item in a large database. > > > > > > -- > View this message in context: &

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Vladimir Stokic
is won't tell you _what_ changed, which might > not help if you're only watching for a tiny item in a large database. > > -- View this message in context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Trevor Talbot
A couple other things come to mind here, that might be relevant to what you're doing: * A trigger that has a "final" side effect, like signaling another process, will both have that effect early (before the transaction is committed), and will have that effect even if the transaction is later rolle

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Vladimir Stokic
you would not have to touch process A. > -- View this message in context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13189204 Sent from the SQLite mailing list

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread John Stanton
Vladimir Stokic wrote: I agree that the solution with the semaphore is a very elegant one, but that still does not solve the problem of having to define the custom function to be called from the trigger over and over again. On the other hand, if the function is not going to be called from the tri

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Vladimir Stokic
context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13188912 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread John Stanton
If you use a semaphore it is independent of the processes currently running and has nothing to do with a Sqlite connection. Your custom function is called, signals the semaphore and exits. When the semaphore is signalled the process waiting on it is activated. In your example myfunc performs

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Trevor Talbot
On 10/13/07, Vladimir Stokic <[EMAIL PROTECTED]> wrote: > I tried to do what you and Igor said, but I found out that it does not really > work that way. I can make a user-defined function, but it stays active only > while the current connection is open. It is not persisted in the "database". > Fr

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Vladimir Stokic
qlite3 > handle which, um, triggers. So if app A makes a change, the trigger > runs in app A, but not in app B. > > You could have a custom function which the trigger invokes to send an > IPC over to app

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread John Stanton
r Stokic -- View this message in context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13177010 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscrib

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-13 Thread Vladimir Stokic
, but then I >> learned that sqlite3_update_hook does not work that way. >> >> If anyone could help me, I would be very grateful. >> >> Thanks in advance, >> Vladimir Stokic >> -- >> View this message in context: >> http://www.nabble.com/Accessing-

Re: [sqlite] Accessing external applications from within SQLite triggers

2007-10-12 Thread Scott Hess
qlite3_update_hook does not work that way. > > If anyone could help me, I would be very grateful. > > Thanks in advance, > Vladimir Stokic > -- > View this message in context: > http://www.nabble.com/Accessing-external-applications-from-within-SQLite-trigg

[sqlite] Re: Accessing external applications from within SQLite triggers

2007-10-12 Thread Igor Tandetnik
Vladimir Stokic wrote: I would like to know if there is any way to access some external application from within an SQLite trigger. Create a custom function doing that, call it from the trigger. Igor Tandetnik - To u

[sqlite] Accessing external applications from within SQLite triggers

2007-10-12 Thread Vladimir Stokic
, Vladimir Stokic -- View this message in context: http://www.nabble.com/Accessing-external-applications-from-within-SQLite-triggers-tf4614175.html#a13177010 Sent from the SQLite mailing list archive at Nabble.com. - To

Re: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Trey Mack
Wesley wrote: As an example: in an instant messenger a new text message arrives. The application puts it into the message log. The chat window automatically updates with the new text. The statistics window about total # of messages updates. Possibly other things happen. The point is I don't

Re: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Wesley W. Terpstra
On Feb 15, 2007, at 4:01 PM, [EMAIL PROTECTED] wrote: "Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: The approach I'd much prefer is to register a trigger to update the GUI... Note that the trigger runs on the client-side of the process that makes the change - not on the client side of the p

RE: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Samuel R. Neff
in our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Wesley W. Terpstra [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 9:33 AM To: sqlite-users@sqlite.org Subject: [sqlite] Triggers+c

Re: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Wesley W. Terpstra
On Feb 15, 2007, at 3:46 PM, Michael Schlenker wrote: Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view

Re: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread drh
"Wesley W. Terpstra" <[EMAIL PROTECTED]> wrote: > > The approach I'd much prefer is to register a trigger to update the > GUI... > Note that the trigger runs on the client-side of the process that makes the change - not on the client side of the process that is implementing the GUI. Neverthele

Re: [sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Michael Schlenker
Wesley W. Terpstra schrieb: I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. [snip] Thus, a window simply provides the VIEW

[sqlite] Triggers+callbacks = GUI?

2007-02-15 Thread Wesley W. Terpstra
I intend to write a GUI application backed by SQL. Several of the windows display status that would best be represented as a database view. What I've been thinking about is how to update the GUI when the view changes. First the obvious approach: polling. Every X seconds re-execute the que

[sqlite] Triggers and prepared statements for implementation of stored-procedures.

2006-03-18 Thread Ran
Hi all, I need to have some stored-procedures. I wonder what is the best way to do it. SQLite3 does not offer stored-procedures, but it offers: 1. Prepared statements. 2. Triggers. In my application I often create temporary tables - which change the schema, at least in the current connection, and

Re: [sqlite] Triggers and TEMP tables: ticket #1689

2006-02-28 Thread K. Haley
[EMAIL PROTECTED] wrote: > A user complains in ticket #1689 that SQLite does not > allow triggers in one database to refer to tables in > a different database or to TEMP tables. ... > Should I remove the tests from SQLite that prevent > triggers in one database from referring to tables in > a diffe

RE: [sqlite] Triggers and TEMP tables: ticket #1689

2006-02-28 Thread Cariotoglou Mike
e IMHO. as for allowing it, well, guns are allowed also, so you can shoot yourself in the foot, if you really want to. > -Original Message- > From: Brad [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 28, 2006 3:28 PM > To: sqlite-users@sqlite.org > Subject: Re: [

Re: [sqlite] Triggers and TEMP tables: ticket #1689

2006-02-28 Thread Brad
Should I remove the tests from SQLite that prevent triggers in one database from referring to tables in a different database? Well, I have a couple of triggers in my SQL Server database that refer to tables in another database, though they are on the same server. Obviously, I can see some util

Re[2]: [sqlite] Triggers and TEMP tables: ticket #1689

2006-02-27 Thread Taka
I definitely second this since I actually got bitten by this last week :-) We split over 2 databases for the same reason as Derrell and always attach after opening them. Surely it's harmless to remove these checks - if something goes wrong, the trigger will just fail normally with a "no such tab

Re: [sqlite] Triggers and TEMP tables: ticket #1689

2006-02-27 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > Should I remove the tests from SQLite that prevent > triggers in one database from referring to tables in > a different database? Or should I leave things as > they are and close ticket #1689 with a remark of > "works as designed". I have had use for triggers that cou

[sqlite] Triggers and TEMP tables: ticket #1689

2006-02-27 Thread drh
A user complains in ticket #1689 that SQLite does not allow triggers in one database to refer to tables in a different database or to TEMP tables. SQLite actually goes to a lot of trouble to detect these kinds of things since, as we were writing the code, we could not think of any circumstance whe

Re: [sqlite] SQLite triggers calling C functions

2004-12-29 Thread D. Richard Hipp
Nathan Conrad wrote: Hi, I am working on a Linux program written in C. I would like to be able to set up a trigger that calls a C function so that my application knows when a certain table has changed so that it can update its UI. Can this be done? Is there sample code somewhere? Use sqlite3_create

[sqlite] SQLite triggers calling C functions

2004-12-29 Thread Nathan Conrad
Hi, I am working on a Linux program written in C. I would like to be able to set up a trigger that calls a C function so that my application knows when a certain table has changed so that it can update its UI. Can this be done? Is there sample code somewhere? -Nathan signature.asc Description:

Re: [sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?

2004-09-18 Thread sporkey
On Sat, Sep 18, 2004 at 04:58:53PM -0400, D. Richard Hipp wrote: > [EMAIL PROTECTED] wrote: > >I am working with triggers on a field defined in a table > >as INTEGER PRIMARY KEY. Agreed, triggers are not fully > >implemented on int primary key; but, I need the autoincrement > >feature. > > > >I

Re: [sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?

2004-09-18 Thread D. Richard Hipp
[EMAIL PROTECTED] wrote: I am working with triggers on a field defined in a table as INTEGER PRIMARY KEY. Agreed, triggers are not fully implemented on int primary key; but, I need the autoincrement feature. I always get -1. Can I depend on that -1 until this feature is implemented? If you spec

[sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?

2004-09-18 Thread sporkey
I am working with triggers on a field defined in a table as INTEGER PRIMARY KEY. Agreed, triggers are not fully implemented on int primary key; but, I need the autoincrement feature. I always get -1. Can I depend on that -1 until this feature is implemented? Here is an example. There are two

Re: [sqlite] Triggers & last_inserted_id

2004-06-07 Thread Dennis Cote
Pix wrote: > Suppose I've some tables like these: > > CREATE TABLE contacts (ID, name, surname); > CREATE TABLE oldContacts (ID, name, surname); > CREATE TABLE messages (message, contactID, contactWasDeleted default > NULL); > > I wrote a trigger similar to this: > > CREATE TRIGGER OnDeleteContact

Re: [sqlite] Triggers & last_inserted_id

2004-06-03 Thread Darren Duncan
At 10:53 AM +0200 6/3/04, Pix wrote: No... I need the id of the row inserted by the TRIGGER... the trigger operations should be atomic, so no other db activity should be a problem... I still don't think you need last_insert_id. Try this in your trigger: INSERT INTO OldContacts (ID, name, surn

Re: [sqlite] Triggers & last_inserted_id

2004-06-02 Thread Darren Duncan
At 5:38 PM +0200 6/2/04, Pix wrote: There is a way to have the right last_inserted_rowid() ? Don't use it. Instead, try substituting the string "old.id" for "last_insert_rowid()". You already know what the contact id is because its in "old.id". Moreover, "last_insert_rowid()" is completely un

[sqlite] Triggers & last_inserted_id

2004-06-02 Thread Pix
Suppose I've some tables like these: CREATE TABLE contacts (ID, name, surname); CREATE TABLE oldContacts (ID, name, surname); CREATE TABLE messages (message, contactID, contactWasDeleted default NULL); I wrote a trigger similar to this: CREATE TRIGGER OnDeleteContact BEFORE DELETE ON Contacts FOR E

Re: [sqlite] Triggers and performances

2004-05-27 Thread Christian Smith
On Wed, 26 May 2004, Pix wrote: >Hi, >I'm using triggers to perform some operations on my SQLite DB. >Particularly I'm using them to easily maintain DB coerency (e.g. if I >delete an item from a table, the trigger will delete all rows from >another table that are referencig to that item). > >My qu

[sqlite] Triggers and performances

2004-05-26 Thread Pix
Hi, I'm using triggers to perform some operations on my SQLite DB. Particularly I'm using them to easily maintain DB coerency (e.g. if I delete an item from a table, the trigger will delete all rows from another table that are referencig to that item). My question is: how much performant the tri

Re: [sqlite] Triggers

2004-01-10 Thread Kurt Welgehausen
Try create trigger itemDelTrg after delete on Items for each row begin delete from Stats where Stats.ID = old.ID; end; Also consider create trigger statInsTrg before insert on Stats for each row when (select count(ID) from Items where ID = new.ID) = 0 begin select raise(ro

[sqlite] Triggers

2004-01-09 Thread Brady Hutmacher (AKA Capt. Hack'em)
I am having trouble getting the exact syntax for a trigger. I'm new to triggers, so I'm sure this will be easy for some of you. Table Items: ID Integer Primary Key ... [rest of fields ] Tables Stats: ID ... rest of fields I want to delete a row in Items and have that trigger the deletion of all