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

[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-- 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 column<>mynewval" for every column in SET
> > to get the actual changes, something like UPDATE testtable SET col1=?1,
> > col2=?2, col3=? WHERE  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

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

[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;
>
> 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 column<>mynewval" for every column in SET
> to get the actual changes, something like UPDATE testtable SET col1=?1,
> col2=?2, col3=? WHERE  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

[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
>> >   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

[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 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

[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 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

[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 WHERE contain many columns? Now I have to add a "WHERE
column<>mynewval" for every column in SET to get the actual changes,
something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE  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

[Black, Michael (IS)]

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.za>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 != 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

[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 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

[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 != 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

[Black, Michael (IS)]

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

[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 :-)
___
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]

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  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

[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 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

[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 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] Update trigger fires for all rows even if sqlite3_changes returns 0

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger

[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 and log the specific details
>> for that column only.
>
> You can compare the old and new values for any column you name 
> (old.columnName and new.columnName) but a single UPDATE command can change 
> any number of columns, so you would have to specifically test for each column 
> inside your TRIGGER, something like
>
> CREATE TRIGGER myTabUpdated
>    AFTER UPDATE OF myTab
>    FOR EACH ROW BEGIN
>    IF old.name <> new.name THEN
>        INSERT INTO changeLog ...;
>    IF old.address <> new.address THEN
>        INSERT INTO changeLog ...;
>    ...
>  END;
>
> Except that there is no such command as 'IF' in SQL.  Each TRIGGER can have 
> only one conditional: a WHEN clause that can go before the BEGIN.

The way to handle the absence of IF here is this:

CREATE TRIGGER ...
BEGIN
  INSERT INTO changeLog SELECT ... WHERE OLD.name IS NOT NEW.name;
  -- or
  INSERT INTO changeLog SELECT ... WHERE OLD.name IS NOT NEW.name AND ...;
  ...
END;

The trick is that you use an INSERT...SELECT/UPDATE/DELETE statement
with a WHERE clause where a conjunction with an expression unrelated
to the row being inserted/updated/deleted is used.  That expression is
likely, and certainly can be, completely unrelated to the rows being
inserted/updated/deleted -- but it should be related to the event that
triggered the trigger.

Sure, you can get a very similar effect with a WHEN clause on a
trigger, but a) you'll need more triggers, b) you get a fairly
artificial constraint on the log schema (OK, not really, but it
requires clever trigger body writing to make the WHEN approach not
constrain the log schema), c) using a WHEN clause is an uncomfortable
straightjacket compared to using WHERE clauses on the
INSERT/UPDATE/DELETE statements in the trigger body.

> You might be able to use CASE to do what you want but you will still need to 
> use several statements in a row, and I'm drawing a blank right now on how it 
> could be done.

You can't have an INSERT/UPDATE/DELETE embedded in a larger SELECT,
which means that you can't use CASE for making conditional changes.
The way to make conditional changes is with WHERE clauses (see above)
(or do it at the application layer, of course).

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update trigger

[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. 

You can compare the old and new values for any column you name (old.columnName 
and new.columnName) but a single UPDATE command can change any number of 
columns, so you would have to specifically test for each column inside your 
TRIGGER, something like

CREATE TRIGGER myTabUpdated
AFTER UPDATE OF myTab
FOR EACH ROW BEGIN
IF old.name <> new.name THEN
INSERT INTO changeLog ...;
IF old.address <> new.address THEN
INSERT INTO changeLog ...;
...
  END;

Except that there is no such command as 'IF' in SQL.  Each TRIGGER can have 
only one conditional: a WHEN clause that can go before the BEGIN.

The UPDATE OF colName ON tableName syntax, and the FOR EACH ROW WHEN ... BEGIN 
syntax both mean you don't have to try specifying conditionals inside your 
TRIGGER.

You might be able to use CASE to do what you want but you will still need to 
use several statements in a row, and I'm drawing a blank right now on how it 
could be done.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update trigger

[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 the primary key name and value).

 

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. 

 

CREATE TRIGGER config_update_log AFTER UPDATE OF hi_flow_rate ON
config_table

BEGIN

INSERT INTO audit_log (audit.name, audit.new_value, audit.old_value,
audit.date_time) VALUES ('High Flow Rate', new.hi_flow_rate,
old.hi_flow_rate, datetime('now','localtime'));

END;

 

I've seen examples where old and new values for all columns were logged
but not where only changed columns were logged.

 

Thanks,

Doug

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update trigger to require input

[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 the column NOT NULL with no DEFAULT.
  You can also add a CHECK constraint to make sure the string isn't
  empty.

  If you do it with triggers instead, make sure you create both update
  and insert triggers.

   -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 to require input

[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.updatedby is not 
a blank and if it is RAISE a FAIL.



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update trigger to require input

[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:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE TRIGGER works in all records

[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 NOT EXISTS t_update_a
> >  AFTER UPDATE ON t
> >   BEGIN
> > UPDATE t SET b = DATETIME('now','localtime') WHERE new.a != old.a;
> >   END;
> 
> Make it
> 
> CREATE TRIGGER IF NOT EXISTS t_update_a
>  AFTER UPDATE OF a ON t
>   BEGIN
> UPDATE t SET b = DATETIME('now','localtime') WHERE rowid=new.rowid;
>   END;
> 
> Igor Tandetnik 
> 
> 

Thanks - that was the solution

First I didn't understand why this could work because the rowid I
queried never changed after an update but after studying the
documentation I discovered that it is only an alias for the real rowid.
Very helpful to know.

Oliver Peters

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE TRIGGER works in all records

[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 = DATETIME('now','localtime') WHERE new.a != old.a;
>   END;

Make it

CREATE TRIGGER IF NOT EXISTS t_update_a
 AFTER UPDATE OF a ON t
  BEGIN
UPDATE t SET b = DATETIME('now','localtime') WHERE rowid=new.rowid;
  END;

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE TRIGGER works in all records

[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:
> 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 NOT NULL,
>   b   TEXT DEFAULT NULL
>   );
>
> CREATE TRIGGER IF NOT EXISTS t_update_a
>  AFTER UPDATE ON t
>  BEGIN
>    UPDATE t SET b = DATETIME('now','localtime') WHERE new.a != old.a;
>  END;
>
> INSERT INTO t
> (a) VALUES('V');
>
> INSERT INTO t
> (a) VALUES('S');
>
> UPDATE t
> SET a = 'K' WHERE a ='S';
>
> SELECT * FROM t;
>
>
> Thank you for your help
> ___
> Nur bis 31.05.: WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate
> und Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/
>
> ___
> 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] UPDATE TRIGGER works in all records

[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 NOT NULL,
   b   TEXT DEFAULT NULL
   );

CREATE TRIGGER IF NOT EXISTS t_update_a
 AFTER UPDATE ON t
  BEGIN
UPDATE t SET b = DATETIME('now','localtime') WHERE new.a != old.a;
  END;

INSERT INTO t
(a) VALUES('V');

INSERT INTO t
(a) VALUES('S');

UPDATE t
SET a = 'K' WHERE a ='S';

SELECT * FROM t;


Thank you for your help
___
Nur bis 31.05.: WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate
und Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 valuable time,

Bharath


On 6/16/08 6:39 PM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> "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 expecting that INSERT OR REPLACE should trigger
>> update_trigger
> 
> Yes. Didn't you just explain why it doesn't happen?
> 
>> when on the whole the operation performed is actually an UPDATE?
> 
> 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.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 expecting that INSERT OR REPLACE should trigger
> update_trigger

Yes. Didn't you just explain why it doesn't happen?

> when on the whole the operation performed is actually an UPDATE?

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.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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, and another, say
update_trigger, set to trigger after UPDATE.

If INSERT OR REPLACE statement performs INSERT operation, the insert_trigger
is being invoked, which is fine.

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 expecting that INSERT OR REPLACE should trigger update_trigger
when on the whole the operation performed is actually an UPDATE?

I apologize, if my question is too silly.

Regards,

Bharath



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update Trigger

[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 a 
>condition, that you also haven't described, that selects which records 
>in the table to update. Generally it will look something like this.

k.say there are 4 records in the MUSIC table This table has only the
Album_id and not the Albumname .But If I want  to update all the AlbumName
for all the records in MUSIC table to only one AlbumName say 'Album1' then
the rest of the AlbmName has to be deleted in the ALBUM Table and Album1 Id
should be provided as Album_Id for all the records in the MUSIC table.

I think then I have to delete all the records in the ALBUM Table and insert
one new record with the new AlbumName.Then I have to update Albim_Id for all
the records in the MUSIC table.Am I right or is there any other way.


>If this isn't what you are looking for, you will have to describe your 
>problem in more detail (i.e. what you are trying to do, an example of 
>before and after data, etc.) before anyone can provide more assistance.
>Original Table Before Update:

 "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
 NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
 
 ArtistId   ArtistName  YomiArtistName  
 10 bbb BBB
 11 xxx XXX
 12 aaa AAA

 "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
 NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
 INTEGER);"
 
 Id Track   YomiTrack  URLAlbum_Id  Artist_Id
 1  trak1   TRAK1 c:/trak1  22   10   
 2  songSONG  c:/song   21   11   
 3  abc ABC   c:/abc23   12   

Delete * from ARTIST.
Insert into ARTIST (ArtistName,YomiArtistName) values ('Album1');
Update MUSIC SET Album_Id = ( select Albumid from ALBUM where ArtistName
='Album1');

Modified Table After Update:

"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
 NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
 
 ArtistId   ArtistName  YomiArtistName  
 1  Album1  ALBUM1

 "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
 NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
 INTEGER);"
 
 Id Track   YomiTrack  URLAlbum_Id  Artist_Id
 1  trak1   TRAK1 c:/trak1  110   
 2  songSONG  c:/song   111   
 3  abc ABC   c:/abc112   


Similarly I will change the artist name of all the records also.
 

Thanks & Regards,
Mahalakshmi




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Trigger

[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
> 11xxx XXX
> 12aaa AAA
> 
> "CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
> NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));"
> 
> AlbumId   AlbumName   YomiAlbumName
> 20zzz ZZZ
> 21ccc CCC
> 22bbb BBB
>   
> "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
> NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
> INTEGER,AlbumArtist_Id INTEGER);"
> 
> Id Track  YomiTrack   URL   Album_Id  Artist_Id
> AlbumArtist_Id
> 1  trak1  TRAK1 c:/trak1  22   10   1
> 2  song   SONG  c:/song   21   11   2
> 3  abcABC   c:/abc23   12   3
> 
> Now I want to Update the AlbumName or ArtistName for all the records in
> MUSIC table . How can I do.If I update All the Records to one New AlbumName
> Then the rest of the AlbumName should be deleted.
> 

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 a 
condition, that you also haven't described, that selects which records 
in the table to update. Generally it will look something like this.

update Album
set AlbumName = (select some_value using music table)
where AlbumId in (select some records using the music table );

If this isn't what you are looking for, you will have to describe your 
problem in more detail (i.e. what you are trying to do, an example of 
before and after data, etc.) before anyone can provide more assistance.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update Trigger

[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  YomiArtistName  
10  bbb BBB
11  xxx XXX
12  aaa AAA

"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));"

AlbumId AlbumName   YomiAlbumName
20  zzz ZZZ
21  ccc CCC
22  bbb BBB

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER,AlbumArtist_Id INTEGER);"

Id TrackYomiTrack   URL   Album_Id  Artist_Id
AlbumArtist_Id
1  trak1TRAK1 c:/trak1  22   10   1
2  song SONG  c:/song   21   11   2
3  abc  ABC   c:/abc23   12   3

Now I want to Update the AlbumName or ArtistName for all the records in
MUSIC table . How can I do.If I update All the Records to one New AlbumName
Then the rest of the AlbumName should be deleted.

Can I use Update Trigger I tried but not working.

Please help to solve this.

Thanks & Regards,
Mahalakshmi



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users