[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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 sure that sqlite3_changes() in this case also returns *all* rows, not only 4 really changed. If you want triggers to only fire for really changed rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE clause: UPDATE table SET column=NULL WHERE column IS NOT NULL; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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 only fire 4 triggers, except it fires for every row. 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. If you only want the trigger to fire for non-NULL rows, you need to update only the non-NULL rows: UPDATE table SET column=NULL WHERE column IS NOT NULL; As for sqlite3_changes() returning 0, that doesn't sound right unless you're checking inside the trigger. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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 to triggers? On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy yum...@gmail.com wrote: 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 sure that sqlite3_changes() in this case also returns *all* rows, not only 4 really changed. If you want triggers to only fire for really changed rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE clause: UPDATE table SET column=NULL WHERE column IS NOT NULL; ___ 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] Update trigger fires for all rows even if sqlite3_changes returns 0
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 :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
What's better is that it tells you what you asked for...not what you think you asked for...which it does. You've already been shown the correct solution...a WHERE clause... You want sqlite to do a complete record compare, including following update triggers, on EVERY record it looks at to see if something happened to change??? Yuck... As compare to the WHERE clause which does exactly what you want and runs oodles faster (in all likelihood). Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information 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 fires for all rows even if sqlite3_changes returns 0 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 to triggers? On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy yum...@gmail.com wrote: 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 sure that sqlite3_changes() in this case also returns *all* rows, not only 4 really changed. If you want triggers to only fire for really changed rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE clause: UPDATE table SET column=NULL WHERE column IS NOT NULL; ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 p...@planetgis.co.zawrote: 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 :-) Just checked MySQL: UPDATE testtable SET testrow=NULL; Affected rows: 40 UPDATE testtable SET testrow=NULL; Affected rows: 0 That is what I'm familiar with. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS) michael.bla...@ngc.comwrote: 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 posting here, just thought it odd. You want sqlite to do a complete record compare, including following update triggers, on EVERY record it looks at to see if something happened to change??? Just the fields in the SET clause. Yuck... As compare to the WHERE clause which does exactly what you want and runs oodles faster (in all likelihood). I always keep an eye on the affected rows to see what my statements have done (whether I used a WHERE or not). Even with a WHERE, I would prefer seeing actual changes :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
And Oracle says the opposite: Yet they all give the same answer when done with update testtable set testrow=null where testrow not null; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL create table testtable(testrow number); Table created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL update testtable set testrow=null; 3 rows updated. SQL update testtable set testrow=null; 3 rows updated. SQL update testtable set testrow=null where testrow is not null; 0 rows updated. So rather than holding your breath for Oracle to change I'd recommend you do it the portable way. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information 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: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 p...@planetgis.co.zawrote: 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 :-) Just checked MySQL: UPDATE testtable SET testrow=NULL; Affected rows: 40 UPDATE testtable SET testrow=NULL; Affected rows: 0 That is what I'm familiar with. ___ 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] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) michael.bla...@ngc.comwrote: 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 WHERE contain many columns? Now I have to add a WHERE columnmynewval for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert complex where clause AND col1?1 AND col2?2 AND col3?3. (passing a null parameter to the above won't even work!) Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL create table testtable(testrow number); Table created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL update testtable set testrow=null; 3 rows updated. SQL update testtable set testrow=null; 3 rows updated. No surprises there. Oracle has never managed to impress me. SQL update testtable set testrow=null where testrow is not null; 0 rows updated. 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 just think about this for sqlite4? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 9:21 AM, Paul van Helden p...@planetgis.co.za 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 just think about this for sqlite4? That's definitely not a correct way. It could be intuitive for those who doesn't know SQL well. But for anybody else it's counter-intuitive and I would be really disappointed if SQLite will implement that. So the answer to your last question (as Michael already said): better to write in SQL what you really want to do and not expect for SQL engine to guess it for you. If you find writing complex WHERE clause too complicated then don't use sqlite3_changes() function. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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 word change means before != after :-) You can argue about the naming of the _change() function all you want. It is a non-standard extension and the function operates as documented. If you want to call it poorly named, go ahead. That doesn't change what it does. There is, however, little argument that the trigger is doing exactly what one would expect. You are applying an update operation to every row, and the trigger is firing for every row. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 10:55 AM, Jay A. Kreibich j...@kreibi.ch 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 column is not important. Well, it is important to me, the word change means before != after :-) You can argue about the naming of the _change() function all you want. It is a non-standard extension and the function operates as documented. If you want to call it poorly named, go ahead. That doesn't change what it does. There is, however, little argument that the trigger is doing exactly what one would expect. You are applying an update operation to every row, and the trigger is firing for every row. BTW, I think you can add to trigger WHEN NEW.column IS NOT OLD.column and it will fire only for rows where column value has really changed (beware IS NOT with arbitrary right side works only on SQLite 3.6.19 and above). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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) michael.bla...@ngc.comwrote: 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! 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-- especially if any of those columns has an index on them. What if the SET and WHERE contain many columns? Then you're asking for a more complex operation. Your SQL gets a bit more complex as well. Now I have to add a WHERE columnmynewval for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert complex where clause AND col1?1 AND col2?2 AND col3?3. (passing a null parameter to the above won't even work!) Well, no, it won't, because you're using the wrong operator. Use WHERE col1 IS NOT ?1 AND... and it all works fine. No surprises there. Oracle has never managed to impress me. I know what you mean. That MySQL database they make is difficult to take seriously. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
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-- especially if any of those columns has an index on them. I gave a simple example. I work with generic cases. My application doesn't have all static SQL. A lot is from the user or built dynamically. What if the SET and WHERE contain many columns? Then you're asking for a more complex operation. Your SQL gets a bit more complex as well. Now I have to add a WHERE columnmynewval for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert complex where clause AND col1?1 AND col2?2 AND col3?3. (passing a null parameter to the above won't even work!) Well, no, it won't, because you're using the wrong operator. Use WHERE col1 IS NOT ?1 AND... and it all works fine. OK thanks, so I should always use IS NOT where I always used . Oh well (talk about yuck!) No surprises there. Oracle has never managed to impress me. I know what you mean. That MySQL database they make is difficult to take seriously. Very funny. They didn't make it, they own it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
