Re: [sqlite] Trigger help needed
Thanks - and with a little reading of the manpages, understandable too Appreciate it. On Wed, Jun 1, 2011 at 2:34 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 6/1/2011 5:23 PM, Jim Mellander wrote: >> I appreciate the help - I'm new at using triggers. What I want to >> accomplish is: >> >> 1. insert if the ip isn't already in the database >> 2. update the timestamp if the new timestamp after the current >> timestamp in the database > > Something like this, perhaps: > > insert or replace into ip_timestamps values(:ip, > max(:timestamp, coalesce((select timestamp from ip_timestamps where > ip = :ip), 0)) > ); > > No trigger needed. > -- > Igor Tandetnik > > ___ > 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] Trigger help needed
I appreciate the help - I'm new at using triggers. What I want to accomplish is: 1. insert if the ip isn't already in the database 2. update the timestamp if the new timestamp after the current timestamp in the database what would be the proper trigger to accomplish this task? Thanks in advance On Wed, Jun 1, 2011 at 1:22 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 6/1/2011 3:31 PM, Jim Mellander wrote: >> CREATE TABLE ip_timestamps (ip text unique, timestamp date); >> CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); >> CREATE TRIGGER ts_update after update on ip_timestamps when >> NEW.timestamp< OLD.timestamp BEGIN update ip_timestamps set timestamp >> = OLD.timestamp; END; > > This updates all rows, not just the one on which the trigger was fired. > Is this intentional? You might want something lile > > update ip_timestamps set timestamp = OLD.timestamp where rowid = NEW.rowid; > > -- > Igor Tandetnik > > ___ > 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
[sqlite] Trigger help needed
Hopefully someone can help me with this I have a table with IP addresses and timestamps - I want to update the table when the new timestamp is later than the old one $ sqlite3 test.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE ip_timestamps (ip text unique, timestamp date); CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); CREATE TRIGGER ts_update after update on ip_timestamps when NEW.timestamp < OLD.timestamp BEGIN update ip_timestamps set timestamp = OLD.timestamp; END; I'm adding/updating records with statements like: INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); The goal is to keep the latest timestamp in the database (the older timestamp could occur later in the input than the newer timestamp), but the trigger doesn't seem to be working - I assume the trigger is flawed. Any suggestions? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] update a record just using a trigger
I appreciate the prompt response to my prior question about triggers,
and have another one.
I'm using a python program to query an SQLite database from realtime
data (IDS data actually). Every time I get a hit in the database, I
want to record in that record an updated hitcount, and the timestamp
that it occured, something like:
cursor.execute("UPDATE detail SET lastaccessed=datetime('now'),
hitcount=? WHERE ROWID=?", [ row["hitcount"]+1, row["ROWID"] ])
where row[] is the row that was read in from the database.
However, I'm wondering if I can just do this via a trigger in the
database, so that I just tell sqlite something like:
cursor.execute("UPDATE detail WHERE ROWID=?", [row["ROWID"] ]);
Can I have a trigger do all the work, or do I need to SET something?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger conditionally causing delete
I have another question on this, if you don't mind (please excuse my ignorance of SQL). I tried your trigger: CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) BEGIN DELETE FROM summary WHERE key = OLD.key; END; and it works as advertised, but I would like to understand why. I'm a bit hazy on why the FOR EACH ROW statement works. Does FOR EACH ROW refer to each row that is being removed from 'detail'? Is it that the statements between BEGIN and END run when the condition 'NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key)' is fulfilled ? Once I grok SQL, I'm sure this will all seem like old hat, but I would appreciate any further clarifications. Thanks in advance. On Fri, Oct 1, 2010 at 3:30 PM, Jim Mellander <jmellan...@lbl.gov> wrote: > Thanks, this is great - I'll read up on expression syntax and usage. > > On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson <dr...@begriffli.ch> wrote: >> Quoth Jim Mellander <jmellan...@lbl.gov>, on 2010-10-01 14:38:03 -0700: >>> Hi: >>> >>> I want to use to trigger on deletion of a detail record to >>> automatically delete a summary record, if not more detail records >>> exist, something like: >>> >>> CREATE TRIGGER detail_delete AFTER DELETE ON detail >>> BEGIN >>> -- here I don't know syntax >>> IF COUNT(detail records with key) = 0 DELETE summary WHERE >>> key=old.key; >>> END; >> >> SQLite triggers don't use an imperative language with full control >> structures, just basic SQL. However, in this case you can use a WHEN >> clause: >> >> CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail >> FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) >> BEGIN >> DELETE FROM summary WHERE key = OLD.key; >> END; >> >> ---> Drake Wilson >> ___ >> 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] Trigger conditionally causing delete
Thanks, this is great - I'll read up on expression syntax and usage. On Fri, Oct 1, 2010 at 3:11 PM, Drake Wilson <dr...@begriffli.ch> wrote: > Quoth Jim Mellander <jmellan...@lbl.gov>, on 2010-10-01 14:38:03 -0700: >> Hi: >> >> I want to use to trigger on deletion of a detail record to >> automatically delete a summary record, if not more detail records >> exist, something like: >> >> CREATE TRIGGER detail_delete AFTER DELETE ON detail >> BEGIN >> -- here I don't know syntax >> IF COUNT(detail records with key) = 0 DELETE summary WHERE >> key=old.key; >> END; > > SQLite triggers don't use an imperative language with full control > structures, just basic SQL. However, in this case you can use a WHEN > clause: > > CREATE TRIGGER auto_delete_summary AFTER DELETE ON detail > FOR EACH ROW WHEN NOT EXISTS (SELECT * FROM detail WHERE key = OLD.key) > BEGIN > DELETE FROM summary WHERE key = OLD.key; > END; > > ---> Drake Wilson > ___ > 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] Trigger conditionally causing delete
As a newbie just learning SQL, this looks like a foreign key situation - unfortunately I'm using the standard snowleopard sqlite which is 3.6.12, which appears to not support foreign key constraints. Assuming I upgrade, I presume I can add the summary record with a BEFORE trigger if it isn't there, and attempt delete of the summary record with an AFTER trigger, which will not occur if the foreign key constraint is set correctly. Would this be an accurate understanding? On Fri, Oct 1, 2010 at 2:38 PM, Jim Mellander <jmellan...@lbl.gov> wrote: > Hi: > > I want to use to trigger on deletion of a detail record to > automatically delete a summary record, if not more detail records > exist, something like: > > CREATE TRIGGER detail_delete AFTER DELETE ON detail > BEGIN > -- here I don't know syntax > IF COUNT(detail records with key) = 0 DELETE summary WHERE key=old.key; > END; > > > Is something like this possible to do? Any suggestions. > > Thanks in advance > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger conditionally causing delete
Hi: I want to use to trigger on deletion of a detail record to automatically delete a summary record, if not more detail records exist, something like: CREATE TRIGGER detail_delete AFTER DELETE ON detail BEGIN -- here I don't know syntax IF COUNT(detail records with key) = 0 DELETE summary WHERE key=old.key; END; Is something like this possible to do? Any suggestions. Thanks in advance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
