SQLite Trigger

As mentioned earlier, the trigger is a named database object (named because every trigger must have a unique name, no two triggers can associated with the same database or table can have the same name) especially used to respond when a specific event has occurred to let the user know that it has been successful. Trigger works with 3 SQLite commands –

• INSERT Trigger: It responds/triggers when some value or a new row is being inserted in the associated table.
• DELETE Trigger: It responds/triggers when some value or an entire row is being deleted from the associated table.
• UPDATE Trigger: It responds/triggers when some value or an entire row is being updated in the associated table.

Syntax:

CREATE TRIGGER  <Trigger_Name> 
   [ AFTER | BEFORE | INSTEAD OF ] [ INSERT | UPDATE | DELETE ] 
   ON <Table_Name>
   WHEN <condition>
BEGIN
 <Statements_Related _to_Condition>
END;

Explanation of the Syntax:

• 1st line: In the very first line we will use the CREATE TRIGGER command and then provide the trigger name.
• 2nd line: In the next line we have to provide the trigger type, means that when the trigger should respond, BEFORE the execution of a supported command or AFTER the execution of a supported command. INSTEAD OF Trigger can only be used in case of a view, not a table. After that we need to provide on which operation a Trigger should execute i.e INSERT, DELETE or UPDATE.
• 3rd Line: Now we have to mention that the trigger will be associated with which table using the ON command.
• 4th Line: Then the condition will be given using the WHEN command, on which a trigger will respond.
• From 5th Line Till End: Now from the 5th line i.e BEGIN we will configure the trigger, means what will the trigger do or how it should respond on execution of the decided command i.e INSERT UPDATE or DELETE. The END command marks the end of the trigger body.

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. The main features of SQLite are that it is a tiny, quick, self-contained, reliable, full-featured SQL database engine.

A trigger is a schema object that gets executed or “triggered” automatically when SQLite commands like INSERT, UPDATE, or DELETE are executed against a certain table. Triggers are mostly used in complex infrastructure that involves several databases and their tables, to keep a log of the changes made in that database or tables. Triggers can also be used to prevent invalid transactions in case of important and sensitive databases or tables.

In this article, We will see how we can create a trigger in SQLite and some of the examples where a trigger can be used, also we will see how we can delete the trigger using the DROP command.