[sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?

[sporkey]

I am working with triggers on a field defined in a table 
as INTEGER PRIMARY KEY.  Agreed, triggers are not fully 
implemented on int primary key; but, I need the autoincrement
feature.

I always get -1. Can I depend on that -1 until this feature
is implemented?


Here is an example. There are two tables, mesg with 
the following triggers:



CREATE TABLE mesg (mkey      INTEGER PRIMARY KEY,
                   mesg      TEXT,
                   timeEnter DATE);

CREATE TRIGGER insert_mesg_timeEnter AFTER INSERT ON mesg
BEGIN
UPDATE mesg SET timeEnter = DATETIME('NOW')  
   WHERE rowid = new.rowid;
END;

And log with the following definition and triggers:


CREATE TABLE log (lkey INTEGER PRIMARY KEY,
                  mkey INTEGER,
                  mesgOLD TEXT,
                  mesgNEW TEXT,
                  sqlType VARCHAR(15),
                  mesgtimeEnter    DATE,
                  mesgtimeUpdate   DATE,
                  timeEnter        DATE);


CREATE TRIGGER update_log UPDATE OF mesg ON mesg
BEGIN
INSERT INTO log  (mkey,mesgOLD,mesgNEW,sqlType,mesgtimeEnter,mesgtimeUpdate,timeEnter)
          values (old.mkey,old.mesg, new.mesg, 
'UPDATE',old.timeEnter,DATETIME('NOW'),DATETIME('NOW') );
END;
--
--  Also create an insert log
CREATE TRIGGER insert_log INSERT ON mesg
BEGIN
INSERT INTO log  (mkey,mesgNEW,sqlType,mesgtimeEnter,timeEnter)
          values (new.mkey ,new.mesg,'INSERT',new.timeEnter,DATETIME('NOW') );
END;

--  Also create a DELETE entry in log
CREATE TRIGGER delete_log DELETE ON mesg
BEGIN
INSERT INTO log  (mkey,mesgOLD,sqlType,timeEnter)
          values (old.mkey,old.mesg,'DELETE',DATETIME('NOW') );
END;


Now, if I issue the following commands:

 $ sqlite3 msgdatabase < mesgScript
 $ sqlite3 msgdatabase "insert into mesg (mesg) values ('My first message to table 
mesg')"
 $ sqlite3 msgdatabase "select * from mesg"
 1|My first message to table mesg|2004-09-18 20:09:46

 $ sqlite3 msgdatabase < logScript
 $ sqlite3 msgdatabase "insert into mesg (mesg) values ('Test log table  message')"
 $ sqlite3 msgdatabase "update mesg set mesg='NEW VALUE from update' where mesg like 
'Test log%'"
 $ sqlite3 msgdatabase "insert into mesg (mesg) values ('will soon delete this')"
 $ sqlite3 msgdatabase "delete from mesg where mesg like 'will soon del%'"


The I will get the following output. 
 

1|-1||Test log table  message|INSERT|||2004-09-18 20:10:39
2|2|Test log table  message|NEW VALUE from update|UPDATE|2004-09-18 
20:10:39|2004-09-18 20:10:47|2004-09-18 20:10:47
3|-1||will soon delete this|INSERT|||2004-09-18 20:10:58
4|3|will soon delete this||DELETE|||2004-09-18 20:11:08

My question: What is meant by strange output in the documentation?
Will I always get -1, which will be the extent of the strange 
behavior, or can I expect anything, any value?

Note the trigger issue is not limited to mkey in mesg. It 
is anything that changes automatically, like timeEnter as 
well. For instance, timeEnter changes in mesg cannot be 
picked up on log, just as mkey cannot.


Regards,

Mike Chirico