On 11/22/2011 3:08 PM, Steffen Mangold wrote:
Ok here the complete example (sorry if I wasn’t clear before):
1. I had a table where I insert some data with a datetime and a value
CREATE TABLE tableA
(
[TimeStamp] datetime,
[Value] varchar
);
2. Now I have a second table where I want save the lowest time insert in
tableA
CREATE TABLE tableB
(
[LowestTime] time,
);
3. To store the time I wrote a trigger for tableA
CREATE TRIGGER tableA _InsertUpdate
AFTER INSERT
ON tableA
begin
update tableB
set
[LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR ([LowestTime]>
TIME(NEW.TimeStamp)) THEN NEW.[TimeStamp] ELSE [LowestTime] END
end;
Do you ever create a record in tableB? UPDATE statement never changes
the number of rows in the table.
Note that you set LowestTime to be equal to TimeStamp, not
time(TimeStamp). It ends up containing a string that encodes both date
and time, not just time.
4. Now I make 2 inserts in tableA (update trigger works because I created
a dummy row to work)
INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 01:00:00’,
‘Dump’ );
After this, LowestTime is set to '2011-01-01 01:00:00'
INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 02:00:00’,
‘Dump’ );
'2011-01-01 01:00:00' is greater (lexicographically, the way strings are
compared) than '02:00:00', so LowestTime is set to '2011-01-01 02:00:00'
5. [LowestTime] should now be ‘01:00:00’ BUT it is ’02:00:00’???????
Are you sure? How do you check this? I would expect it to be neither,
but instead have a value of '2011-01-01 02:00:00'.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
