Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?

[Gilles Ganault]

At 02:21 06/08/2007 +0200, you wrote:
I'm sorry to bother you again, but I can't figure out how to write a 
trigger that will set a column to 1 when a user creates a new record :-/


Found it in "Sam's Sqlite (2004)":

CREATE TRIGGER insert_mytable_timestamp AFTER INSERT ON mytable
BEGIN
UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid;
END;

I think it'd be a good thing to add examples in the page:

http://sqlite.org/lang_createtrigger.html

G.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?

[Gilles Ganault]

Hello

At 15:28 04/08/2007 -0400, you wrote:

UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid;


I'm sorry to bother you again, but I can't figure out how to write a 
trigger that will set a column to 1 when a user creates a new record :-/


I tried the following but neither works:


CREATE TRIGGER insert_mytable_timestamp INSERT ON mytable
BEGIN
INSERT INTO mytable (timestamp) values (1);
END;


CREATE TRIGGER insert_mytable_timestamp INSERT ON mytable
BEGIN
UPDATE mytable SET timestamp = 1 WHERE rowid=new.rowid;
END;


Thanks,


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?

[Gilles Ganault]

At 15:28 04/08/2007 -0400, Igor Tandetnik wrote:

UPDATE mytable SET timestamp = old.timestamp + 1 WHERE rowid=new.rowid;


Great :-) Thanks a lot. For those interested in doing the same thing:

=
create table mytable (id INTEGER AUTO PRIMARY KEY, name VARCHAR(50), 
timestamp INTEGER);


CREATE TRIGGER update_timestamp UPDATE ON mytable
BEGIN
UPDATE mytable SET timestamp = old.timestamp + 1 WHERE 
rowid=new.rowid;

END;

insert into mytable values (NULL,'Marge',1);

select * from mytable;

update mytable set name='Homer' where id=1;

select * from mytable;
=

Since I'm at it, I'll check if I can have SQLite put a timestamp equal to 1 
when creating a new record so the user doesn't have to.


Thanks everyone
G.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: [2.8.x] Incrementing a column automatically with INSERT/UPDATE?

[Igor Tandetnik]

Gilles Ganault 
wrote: 

So, I guess the solution is to write a trigger. Problem is, I've
never used 
triggers before, so I don't really know how to get around to fetching
the 
previous value of a timestamp column and increment it whenever a
record is 
updated:


CREATE TRIGGER update_timestamp UPDATE ON mytable
  BEGIN
UPDATE mytable SET timestamp = old.timestamp + 1;
  END;


UPDATE mytable SET timestamp = old.timestamp + 1
WHERE rowid=new.rowid;

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-