Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
> > > Then why do you keep hammering on the idea that SQLite is somehow > incorrect or wrong? > > You've explained what you're trying to do. We've explained there is > a better way to do that, that also happens to provide the correct > answer on all platforms, AND likely runs faster-- es

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich
On Tue, Jul 03, 2012 at 03:21:57PM +0200, Paul van Helden scratched on the wall: > On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) > wrote: > > > And Oracle says the opposite: > > > > Yet they all give the same answer when done with "update testtable set > > testrow=null where testrow not null

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Pavel Ivanov
On Tue, Jul 3, 2012 at 10:55 AM, Jay A. Kreibich wrote: > On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the > wall: > >> > The statement "UPDATE table SET column=NULL" updates every row in the >> > table. The fact that some rows may already have a NULL in that >> >

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich
On Tue, Jul 03, 2012 at 02:43:29PM +0200, Paul van Helden scratched on the wall: > > The statement "UPDATE table SET column=NULL" updates every row in the > > table. The fact that some rows may already have a NULL in that > > column is not important. > > > > Well, it is important to me, the

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Pavel Ivanov
On Tue, Jul 3, 2012 at 9:21 AM, Paul van Helden wrote: >> So rather than holding your breath for Oracle to change I'd recommend you >> do it the portable way. >> > I'm not waiting for anything. My last question was simple: which is > better? Since MySQL does it the "correct way" perhaps we can jus

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) wrote: > And Oracle says the opposite: > > Yet they all give the same answer when done with "update testtable set > testrow=null where testrow not null; > > You keep hammering this one, it is obvious, I understand, THANKS! What if the SET and WH

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Black, Michael (IS)
den [p...@planetgis.co.za] Sent: Tuesday, July 03, 2012 7:47 AM To: j...@kreibi.ch; General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0 On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote: > >> The

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS) wrote: > What's better is that it tells you what you asked for...not what you think > you asked for...which it does. > I asked for changes :-) > > You've already been shown the correct solution...a WHERE clause... > > I've done that even before

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden wrote: > >> The statement "UPDATE table SET column=NULL" updates every row in the >> table. The fact that some rows may already have a NULL in that >> column is not important. >> >> Well, it is important to me, the word "change" means before !

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Black, Michael (IS)
on Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Paul van Helden [p...@planetgis.co.za] Sent: Tuesday, July 03, 2012 7:39 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Update trigger

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
> > > The statement "UPDATE table SET column=NULL" updates every row in the > table. The fact that some rows may already have a NULL in that > column is not important. > > Well, it is important to me, the word "change" means before != after :-) ___

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
You are right, sorry, just checked. sqlite3_changes returns number of records hit, not changed. Have been using sqlite for 2 years now and was always under the impression this was for actual changes. But which is better behaviour, reporting "row hits" versus real changes? Especially when it comes

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Jay A. Kreibich
On Tue, Jul 03, 2012 at 01:32:14PM +0200, Paul van Helden scratched on the wall: > Hi, > > Is this correct? Should update triggers not only fire for actual changes? I > have a large table with a column which contains all NULL values except for > 4. I expected an UPDATE table SET column=NULL to onl

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Yuriy Kaminskiy
Paul van Helden wrote: > Is this correct? Should update triggers not only fire for actual changes? I > have a large table with a column which contains all NULL values except for > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, > except it fires for every row. I'm pretty sur

[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

2012-07-03 Thread Paul van Helden
Hi, Is this correct? Should update triggers not only fire for actual changes? I have a large table with a column which contains all NULL values except for 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, except it fires for every row. Thanks, Paul. _

Re: [sqlite] Update trigger

2011-03-29 Thread Nico Williams
On Tue, Mar 29, 2011 at 11:32 AM, Simon Slavin wrote: > On 29 Mar 2011, at 4:12pm, Sutter, Doug wrote: >> I know how to create a unique trigger for each column where I hard-code >> the column's name as shown below.  But I would like to create a trigger >> that would fire when any column is updated

Re: [sqlite] Update trigger

2011-03-29 Thread Simon Slavin
On 29 Mar 2011, at 4:12pm, Sutter, Doug wrote: > I know how to create a unique trigger for each column where I hard-code > the column's name as shown below. But I would like to create a trigger > that would fire when any column is updated and log the specific details > for that column only. Yo

[sqlite] Update trigger

2011-03-29 Thread Sutter, Doug
Hi, I am trying to create a trigger that will log changes made to my database. I need to log only the columns that were updated (not the entire record). So I need to be able to log the column name, old value, new value and date/time. I also need to know which row was updated (identified by t

Re: [sqlite] update trigger to require input

2010-07-11 Thread Jay A. Kreibich
On Sun, Jul 11, 2010 at 01:46:19PM -0400, Sam Carleton scratched on the wall: > I have some audit fields, one being updatedby, I would like to create an > update trigger that would prevent the row from being updated if this was not > set. Can I do that in sqlite? Define "not set." You can make

Re: [sqlite] update trigger to require input

2010-07-11 Thread Simon Slavin
On 11 Jul 2010, at 6:46pm, Sam Carleton wrote: > I have some audit fields, one being updatedby, I would like to create an > update trigger that would prevent the row from being updated if this was not > set. Can I do that in sqlite? Use a TRIGGER on BEFORE UPDATE. In the TRIGGER check that new

[sqlite] update trigger to require input

2010-07-11 Thread Sam Carleton
I have some audit fields, one being updatedby, I would like to create an update trigger that would prevent the row from being updated if this was not set. Can I do that in sqlite? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:80

Re: [sqlite] UPDATE TRIGGER works in all records

2009-06-02 Thread Oliver Peters
Am Montag, den 01.06.2009, 07:38 -0400 schrieb Igor Tandetnik: > Oliver Peters wrote: > > After an UPDATE in a record I want the update time stored in a column > > of this record - the problem is that the trigger I use doesn't work > > only in this record but in all others > > > > CREATE TRIGGER IF

Re: [sqlite] UPDATE TRIGGER works in all records

2009-06-01 Thread Igor Tandetnik
Oliver Peters wrote: > After an UPDATE in a record I want the update time stored in a column > of this record - the problem is that the trigger I use doesn't work > only in this record but in all others > > CREATE TRIGGER IF NOT EXISTS t_update_a > AFTER UPDATE ON t > BEGIN > UPDATE t SET b

Re: [sqlite] UPDATE TRIGGER works in all records

2009-06-01 Thread Pavel Ivanov
Your trigger basically does this: UPDATE t SET b = DATETIME('now','localtime') WHERE 1 != 0; So it updates all rows in the table. Try to change it to this: UPDATE t SET b = DATETIME('now','localtime') WHERE rowid = new.rowid; Pavel On Sun, May 31, 2009 at 7:44 AM, Oliver Peters wrote: > Afte

[sqlite] UPDATE TRIGGER works in all records

2009-06-01 Thread Oliver Peters
After an UPDATE in a record I want the update time stored in a column of this record - the problem is that the trigger I use doesn't work only in this record but in all others Here's my script for reproduction: - CREATE TABLE IF NOT EXISTS t( a TEXT

Re: [sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

2008-06-16 Thread Bharath Booshan L
> Well, if I epxlicitly run DELETE and then INSERT, would you also expect > an UPDATE trigger to run? After all, "on the whole" the operation is an > update. Oh!! May be I was wrong in thinking that INSERT OR REPLACE would keep the PRIMARY KEY as it is.. Am a stupid guy.. Thanks Igor for your va

Re: [sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

2008-06-16 Thread Igor Tandetnik
"Bharath Booshan L" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > IF INSERT OR REPLACE statement performs REPLACE operation, then again > insert_trigger is being invoked, which as per the documentation ( i.e > it > actually performs DELETE and INSERT) is fine. > > Am I wrong in expe

[sqlite] UPDATE TRIGGER not called on INSERT OR REPLACE statement

2008-06-16 Thread Bharath Booshan L
Hello Sqlite users, experts, I am in a state of confusion and I request you to help me out please. Can "INSERT OR REPLACE" trigger if actual operation performed is REPLACE? Say , For example, if I have 2 triggers on table T1, one trigger, say insert_trigger, is set to trigger after INSERT, a

[sqlite] Update Trigger

2008-04-04 Thread Mahalakshmi.m
Dennis Wrote: >If you want to update the AlbumName field, you must do that with an >update statement running on the Album table, not the Music table, since >that is where the AlbumName field is stored. You haven't said what you >want to update the AlbumName or ArtistName to. You probably have

Re: [sqlite] Update Trigger

2008-04-04 Thread Dennis Cote
Mahalakshmi.m wrote: > > "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT > NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));" > > ArtistId ArtistName YomiArtistName > 10bbb BBB

[sqlite] Update Trigger

2008-04-04 Thread Mahalakshmi.m
Hi, I am having 4 records in my database. I am using Joins method. My Table Looks like: "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));" ArtistIdArtistName Yo