Article Index
SQLite Tutorial
Logging All Inserts, Updates, and Deletes
UTC and Localtime
The ATTACH Command
The Power of the Sign Function
Creating a Permanent Sign Function
Spreadsheet Format to Normalized Data
C and C API
A C Program -- Building a Class to Do the Work
Defining SQLite User Functions
Aggregate Functions
Reading Images (Blob data)

The script below creates the table examlog and three triggers (update_examlog, insert_examlog, and delete_examlog) to record updates, inserts, and deletes made to the exam table. In other words, whenever a change is made to the exam table, the changes will be recorded in the examlog table, including the old value and the new value. If you are familiar with MySQL, the functionality of this log table is similar to MySQL's binlog. See Tips 2, 24, and 25 if you would like more information on MySQL's log file.

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

Since the script above has been created in the file examLOG, you can execute the commands in sqlite3 as shown below. Also shown below is a record insert, and an update to test these newly-created triggers.

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

Now, by doing the select statement below, you will see that examlog contains an entry for the insert statement, plus two updates. Although we only did one update on the commandline, the trigger "insert_exam_timeEnter" performed an update for the field timeEnter; this was the trigger defined in "examScript". In the second update, we can see that the score has been changed. The trigger is working. Any change made to the table, whether by user interaction or another trigger, is recorded in the examlog.

$ sqlite3 examdatabase "select * from examlog"

1|2||Bob||Anderson||2||80||INSERT|||2004-10-02 15:33:16
2|2|2|Bob|Bob|Anderson|Anderson|2|2|80|80|UPDATE||2004-10-02 15:33:16|2004-10-02 15:33:16
3|2|2|Bob|Bob|Anderson|Anderson|2|2|82|80|UPDATE|2004-10-02 15:33:16|2004-10-02 15:33:26|2004-10-02 15:33:26

Again, pay particular attention to the AFTER keyword. Remember that by default, triggers are BEFORE, so you must specify AFTER to insure that all new values will be available if your trigger needs to work with any new values.