SQLite Triggers are information base callback capacities, which are naturally performed/summoned when a predefined data set occasion happens. Following are the significant focuses about SQLite triggers −
- SQLite trigger might be indicated to fire at whatever point a DELETE, INSERT or UPDATE of a specific data set table happens or at whatever point an UPDATE happens on at least one determined segments of a table.
- As of now, SQLite bolsters just FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Thus, unequivocally indicating FOR EACH ROW is discretionary.
- Both the WHEN provision and the trigger activities may get to components of the line being embedded, erased, or refreshed utilizing references of the structure NEW.column-name and OLD.column-name, where segment name is the name of a section from the table that the trigger is related with.
- On the off chance that a WHEN proviso is provided, the SQL explanations indicated are possibly executed for columns for which the WHEN condition is valid. On the off chance that no WHEN condition is provided, the SQL articulations are executed for all lines.
- The BEFORE or AFTER catchphrase decides when the trigger activities will be executed comparative with the addition, change, or evacuation of the related line.
- Triggers are naturally dropped when the table that they are related with is dropped.
- The table to be altered should exist in a similar data set as the table or view to which the trigger is joined and one should utilize just tablename not database.tablename.
- A unique SQL work RAISE() might be utilized inside a trigger-program to raise an exemption.
Syntax
Following is the fundamental punctuation of making a trigger.
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
Here, event_name could be INSERT, DELETE, and UPDATE information base procedure on the referenced table table_name. You can alternatively determine FOR EACH ROW after table name.
Following is the punctuation for making a trigger on an UPDATE procedure on at least one indicated segments of a table.
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
Example
Allow us to consider a situation where we need to keep review preliminary for each record being embedded in COMPANY table, which we make recently as follows (Drop COMPANY table in the event that you as of now have it).
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
To keep review preliminary, we will make another table called AUDIT where the log messages will be embedded, at whatever point there is a section in COMPANY table for another record.
sqlite> CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be made in COMPANY table. Presently we should make a trigger on COMPANY table as follows −
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
Presently, we will begin real work, Let's beginning embeddings record in COMPANY table which should bring about making a review log record in AUDIT table. Make one record in COMPANY table as follows −
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
This will make one record in COMPANY table, which is as per the following −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
Same time, one record will be made in AUDIT table. This record is the consequence of a trigger, which we have made on INSERT activity in COMPANY table. Additionally, you can make your triggers on UPDATE and DELETE activities dependent on your prerequisites.
EMP_ID ENTRY_DATE
---------- -------------------
1 2013-04-05 06:26:00
Listing Triggers
You can list down all the triggers from sqlite_master table as follows −
sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';
The above SQLite explanation will list down just a single passage as follows −
name
----------
audit_log
On the off chance that you need to list down triggers on a specific table, at that point use AND condition with table name as follows −
sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';
The above SQLite explanation will likewise list down just a single passage as follows −
name
----------
audit_log
Dropping Triggers
Following is the DROP order, which can be utilized to drop a current trigger.
sqlite> DROP TRIGGER trigger_name;