Basic SQL

Introduction

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed.

Below we discuss the trigger, which perform a very important functions: it automatically inserts a time stamp for a record.

Diary project

Start a new project:

sqlite3 diary.sl3

Using a text editor create notes.sql file:

-- ***************************************************************
-- notes.sql - the script for creating notes table
--
-- The trigger insert_notes_timeStamp updates timeStamp in notes
-- ***************************************************************
CREATE TABLE notes (id        INTEGER PRIMARY KEY,
                    note      TEXT,
                    timeStamp DATE);

CREATE TRIGGER insert_notes_timeStamp AFTER INSERT ON notes
BEGIN
  UPDATE notes SET timeStamp = DATETIME('NOW')
  WHERE rowid = new.rowid;
END;
-- ***************************************************************1 

The Coordinated Universal Time (UTC) will be entered into the field "timeStamp", and this trigger will fire after a row has been inserted into the table notes.

Execute this SQL:

sqlite> .read notes.sql     

You can query the result by executing 

sqlite> .schema   

Now we populate the table and check the contents:

sqlite> INSERT INTO notes (note) VALUES ('Nothing interesting happened today');
sqlite> select * from notes; 

It gives: 

1|Nothing interesting happened today|2005-12-14 20:27:03     

 

More examples

In progress . . . So far, check the links below.

 

References

• CREATE TRIGGER. http://www.sqlite.org/lang_createtrigger.html 
• SQLite Tutorial by Mike Chirico. mchirico@users.sourceforge.net, http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html 
• Enforce Foreign Key Integrity in SQLite with Triggers. http://www.justatheory.com/computers/databases/sqlite/  
• SQLite Optimization FAQ. http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html  
• Frequently Asked Questions, http://www.sqlite.org/faq.html