SQLite INSTEAD OF Triggers
Summary: in this tutorial, you will learn about the SQLite INSTEAD OF
triggers and how to create an INSTEAD OF
trigger to insert data into base tables through a view.
What are INSTEAD OF
triggers in SQLite
In SQLite, an INSTEAD OF
trigger can be only created based on a view, not a table.
Views are read-only in SQLite. And if you issue a DML statement such as INSERT
, UPDATE
, or DELETE
against a view, you will receive an error.
When a view has an INSTEAD OF
trigger, the trigger will fire when you issue a corresponding DML statement. This allows you to inject your own logic in the processing flow.
For example, if a view has an INSTEAD OF INSERT
trigger, when you issue an INSERT
statement, the trigger will fire automatically. Inside the trigger, you can perform insert, update, or delete data in the base tables.
In other words, the INSTEAD OF
triggers allow views to become modifiable.
The following illustrates the syntax of creating an INSTEAD OF
trigger in SQLite:
CREATE TRIGGER [IF NOT EXISTS] schema_ame.trigger_name
INSTEAD OF [DELETE | INSERT | UPDATE OF column_name]
ON table_name
BEGIN
-- insert code here
END;
In this syntax:
- First, specify the name of the trigger after the
CREATE TRIGGER
keywords. UseIF NOT EXISTS
if you want to create the trigger if it exists only. - Second, use the
INSTEAD OF
keywords followed by a triggering event such asINSERT
,UPDATE
, orDELETE
. - Third, specify the name of the view to which the trigger belongs.
- Finally, specify the code that executes the logic.
SQLite INSTEAD OF
trigger example
For the demonstration, we will use the Artists
and Albums
tables from the sample database.
First, create a view named AlbumArtists
based on data from the Artists
and Albums
tables:
CREATE VIEW AlbumArtists(
AlbumTitle,
ArtistName
) AS
SELECT
Title,
Name
FROM
Albums
INNER JOIN Artists USING (ArtistId);
Second, use this query to verify data from the view:
SELECT * FROM AlbumArtists;
Third, insert a new row into the AlbumArtists
view:
INSERT INTO AlbumArtists(AlbumTitle,ArtistName)
VALUES('Who Do You Trust?','Papa Roach');
SQLite issued the following error:
[SQLITE_ERROR] SQL error or missing database (cannot modify AlbumArtists because it is a view)
Fourth, create an INSTEAD OF
trigger that fires when a new row is inserted into the AlbumArtists
table:
CREATE TRIGGER insert_artist_album_trg
INSTEAD OF INSERT ON AlbumArtists
BEGIN
-- insert the new artist first
INSERT INTO Artists(Name)
VALUES(NEW.ArtistName);
— use the artist id to insert a new albumINSERT INTO Albums(Title, ArtistId)
VALUES(NEW.AlbumTitle, last_insert_rowid());
END;
In this trigger:
- First, insert a new row into the
Artists
table. SQLite automatically generates an integer for theArtistId
column. - Second, use the
last_insert_rowid()
to get the generated value from theArtistId
column and insert a new row into theAlbums
table.
Finally, verify insert from the Artists
and Albums
tables:
SELECT
*
FROM
Artists
ORDER BY
ArtistId DESC;
SELECT*
FROM
Albums
ORDER BY
AlbumId DESC;
As you can see the output, a new row has been inserted into the Artists
and Albums
tables.
In this tutorial, you have learned about the SQLite INSTEAD OF
triggers and how to create an INSTEAD OF INSERT
trigger to insert data into base tables through a view.