Logging

lhp@nereida:~/Lperl/src/SQLITE/examples$ cat examScript
-- *******************************************************************
--  examScript: Script for creating exam table
--   Usage:
--       $ sqlite3 examdatabase < examScript
--
--   Note: The trigger insert_exam_timeEnter
--          updates timeEnter in exam
-- *******************************************************************
-- *******************************************************************
CREATE TABLE exam (ekey      INTEGER PRIMARY KEY,
                   fn        VARCHAR(15),
                   ln        VARCHAR(30),
                   exam      INTEGER,
                   score     DOUBLE,
                   timeEnter DATE);

CREATE TRIGGER insert_exam_timeEnter AFTER  INSERT ON exam
BEGIN

UPDATE exam SET timeEnter = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;
-- *******************************************************************
-- *******************************************************************

$ sqlite3 examdatabase < examScript
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values ('Anderson','Bob',1,75)"
$ sqlite3 examdatabase "select * from exam"
1|Bob|Anderson|1|75.0|2007-08-17 11:37:59

-- *******************************************************************
--  examLog: Script for creating log table and related triggers
--   Usage:
--       $ sqlite3 examdatabase < examLOG
--
--
-- *******************************************************************
-- *******************************************************************
CREATE TABLE examlog (lkey INTEGER PRIMARY KEY,
                  ekey INTEGER,
                  ekeyOLD INTEGER,
                  fnNEW   VARCHAR(15),
                  fnOLD   VARCHAR(15),
                  lnNEW   VARCHAR(30),
                  lnOLD   VARCHAR(30),
                  examNEW INTEGER,
                  examOLD INTEGER,
                  scoreNEW DOUBLE,
                  scoreOLD DOUBLE,
                  sqlAction VARCHAR(15),
                  examtimeEnter    DATE,
                  examtimeUpdate   DATE,
                  timeEnter        DATE);

--  Create an update trigger
CREATE TRIGGER update_examlog AFTER UPDATE  ON exam
BEGIN

  INSERT INTO examlog  (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD,
                        lnNEW,examOLD,examNEW,scoreOLD,
                        scoreNEW,sqlAction,examtimeEnter,
                        examtimeUpdate,timeEnter)

          values (new.ekey,old.ekey,old.fn,new.fn,old.ln,
                  new.ln,old.exam, new.exam,old.score,
                  new.score, 'UPDATE',old.timeEnter,
                  DATETIME('NOW'),DATETIME('NOW') );

END;
--
--  Also create an insert trigger
--    NOTE  AFTER keyword ------v
CREATE TRIGGER insert_examlog AFTER INSERT ON exam
BEGIN
INSERT INTO examlog  (ekey,fnNEW,lnNEW,examNEW,scoreNEW,
                      sqlAction,examtimeEnter,timeEnter)

          values (new.ekey,new.fn,new.ln,new.exam,new.score,
                  'INSERT',new.timeEnter,DATETIME('NOW') );

END;

--  Also create a DELETE trigger
CREATE TRIGGER delete_examlog DELETE ON exam
BEGIN

INSERT INTO examlog  (ekey,fnOLD,lnNEW,examOLD,scoreOLD,
                      sqlAction,timeEnter)

          values (old.ekey,old.fn,old.ln,old.exam,old.score,
                  'DELETE',DATETIME('NOW') );

END;
-- *******************************************************************
-- *******************************************************************

$ sqlite3 examdatabase < examLOG
$
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score) values  ('Anderson','Bob',2,80)"
$ sqlite3 examdatabase "update exam set score=82 where ln='Anderson' and fn='Bob' and exam=2"
$ sqlite3 examdatabase "select * from examlog"
1|2||Bob||Anderson||2||80.0||INSERT|||2007-08-17 11:44:32
2|2|2|Bob|Bob|Anderson|Anderson|2|2|80.0|80.0|UPDATE||2007-08-17 11:44:32|2007-08-17 11:44:32
3|2|2|Bob|Bob|Anderson|Anderson|2|2|82.0|80.0|UPDATE|2007-08-17 11:44:32|2007-08-17 11:45:07|2007-08-17 11:45:07