SQLite Triggers is a sql series of sql statements that are
executed automatically to perform/invoked when a specified database event
occurs, such as creation or updating record or deletion of record/table.
Each trigger will have a unique name to the database. If we dropped the table
the associated triggers also be deleted or we can drop the trigger with our
self using ‘DROP TRIGGER’ statement.
If we create a trigger then it can’t be modified, or if we
want changes into our created trigger then at first drop the trigger and then
Here we specifying some important points about SQLite
· A trigger may be specified to fire whenever a
DELETE, INSERT or UPDATE of a particular database table occurs, or whenever an
UPDATE of one or more specified columns of a table are updated.
· SQLite supports only FOR EACH ROW triggers, not
FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is
· Both the WHEN clause and the trigger actions can
access elements of the row being inserted, deleted or updated using references
of the formNEW.column-name and OLD.column-name, where
column-name is the name of a column from the table that the trigger is
· If a WHEN clause is supplied, the SQL statements
specified are only executed for rows for which the WHEN clause is true. If no
WHEN clause is supplied, the SQL statements are executed for all rows.
· The BEFORE or AFTER keyword determines when the
trigger actions will be executed relative to the insertion, modification or
removal of the associated row.
· If the table is dropped then the Triggers are
· The table to be modified must exist in the same
database as the table or view to which the trigger is attached and one must use
justtablename not database.tablename.
· To raise an exception within a trigger-program
an special SQL function RAISE() is available.
· The syntax to create a trigger on a table is as
CREATE [TEMP |
TEMPORARY] TRIGGER trigger-name
[BEFORE | AFTER]
database-event ON [database-name .]table-name trigger-action.
· The trigger-name is user-specified and must be
unique across all objects in the database—it cannot share the same name as a
table, view, or index.
· The trigger-action is further defined as
[FOR EACH ROW | FOR EACH STATEMENT] [WHEN expression]
trigger-step; [trigger-step;] *
· As you might expect, the syntax to drop a
trigger is simply
DROP TRIGGER [database-name .] table-name
· If you forget the name of a trigger, you can
query sqlite_master using type = 'trigger' to find all the triggers on the
Next, we will learn how to use triggers : SQLite- TRIGGERS EXAMPLE