SQLite views, triggers, transactions
In this part of the SQLite tutorial, we will mention views, triggers, and transactions.
A view is a specific look on data in from one or more tables. It can arrange data in some specific order or highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables, a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database.
In the next example, we create a simple view.
sqlite> SELECT * FROM Cars; Id Name Price ----------- ----------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
This is our data, upon which we create the view.
sqlite> CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Price < 30000; sqlite> SELECT * FROM CheapCars; Name ----------- Skoda Volvo Citroen Volkswagen
CREATE VIEW statement is used to create a view.
sqlite> .tables Books CheapCars Friends Names Reservations Cars Customers Log Orders Testing sqlite> DROP VIEW CheapCars; sqlite> .tables Books Customers Log Orders Testing Cars Friends Names Reservations
Technically a view is a virtual table. So we can list all views with a
.tables command. To remove a view, we use the
DROP VIEW statement.
Triggers are database operations that are automatically performed when a specified database event occurs.
In the following example, we will use the
Friends table and
create a new
sqlite> CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, ...> NewName TEXT, Date TEXT);
Log table has a column for the old name and for the new name
of a friend. It also has a column for a timestamp.
CREATE TRIGGER mytrigger UPDATE OF Name ON Friends BEGIN INSERT INTO Log(OldName, NewName, Date) VALUES (old.Name, new.Name, datetime('now')); END;
We create a trigger called
mytrigger with the
statement. This trigger will launch an
INSERT statement whenever we update
Name column of the
INSERT statement will insert the old name, the new name, and the time
stamp into the
Log table. The
references to the row being modified.
sqlite> SELECT name, tbl_name FROM sqlite_master WHERE type='trigger'; name tbl_name ----------- ----------- mytrigger Friends
To list available triggers and their corresponding tables, we query the
sqlite> SELECT * FROM Friends; Id Name Sex ---------- ---------- ---------- 1 Jane F 2 Thomas M 3 Franklin M 4 Elisabeth F 5 Mary F 6 Lucy F 7 Jack M
This is our data.
Next, we are going to update one row of the
sqlite> UPDATE Friends SET Name='Frank' WHERE Id=3;
We update the third row of the table. The trigger is launched.
sqlite> SELECT * FROM Log; Id OldName NewName Date ----------- ----------- ---------- ------------------- 1 Franklin Frank 2014-11-18 10:58:46
We check the
Log table. This log confirms the update operation
A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.
In SQLite, any command other than
SELECT starts an implicit transaction.
Manual transactions are started with the
BEGIN TRANSACTION statement
and finished with the
BEGIN TRANSACTION; CREATE TABLE Test(Id INTEGER NOT NULL); INSERT INTO Test VALUES(1); INSERT INTO Test VALUES(2); INSERT INTO Test VALUES(3); INSERT INTO Test VALUES(NULL); COMMIT;
Here we have a sample transaction. A transaction begins with
BEGIN TRANSACTION and ends with
We have a
NOT NULL constraint set on the Id column. Thus, the fourth insert
will not succeed. SQLite manages transactions on a case-by-case basis. For some errors,
it reverts all changes. For others, it reverts only the last statement and leaves
other changes intact. In our case, the table is created and the first three
inserts are written into the table. The fourth one is not.
Say, we already had an empty table named Test. Executing the above transaction
would fail completely. No changes would be written. If we changed the
CREATE TABLE statement into
CREATE TABLE IF NOT EXISTS,
the first three statements would execute.
BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS Test(Id INTEGER NOT NULL); INSERT INTO Test VALUES(11); INSERT INTO Test VALUES(12); INSERT INTO Test VALUES(13); INSERT INTO Test VALUES(NULL); ROLLBACK;
A transaction can end with a
COMMIT or a
ROLLBACK reverts all changes.
In this part of the SQLite tutorial, we have worked with views, triggers, and transactions in SQLite.