May 16, 2021 SQLite
The trigger for SQLite is the callback function of the database, which automatically executes/calls when the specified database event occurs. H ere are the key points about SQLite triggers: The SQLite trigger is a callback function for a database that automatically executes/calls when a specified database event occurs. Here are the key points about SQLite's Trigger:
The trigger for SQLite can specify whether to trigger when DELETE, INSERT, or UPDATE occurs on a particular database table, or when one or more columns of a specified table are updated.
SQLite only supports for EACH ROW triggers, not FOR EACH STATEMENT triggers. Therefore, it is optional to explicitly specify FOR EACH ROW.
The WHEN clause and trigger actions may access row elements inserted, deleted, or updated using references to forms NEW.column-name and OLD.column-name, where the column-name is the name of the column from the table associated with the trigger.
If the WHEN clause is provided, SQL statements are executed only for lines that are true for the WHEN clause. If the WHEN clause is not provided, sql statements are executed for all rows.
The BEFORE or AFTER keyword determines when a trigger action is performed, before or after the associated row is inserted, modified, or deleted.
When a table associated with a trigger is deleted, the trigger is automatically deleted.
The table to be modified must exist in the same database, be attached as a trigger, and must use tablename only, not database.tablename.
A special SQL function, RAISE(), can be used to throw exceptions within a trigger program.
The basic syntax for creating a trigger is as follows:
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END;
Here, event_name can be INSERT, DELETE, table_name database operations on the table list mentioned. You can choose to specify FOR EACH ROW after the table name.
Here is the syntax for creating triggers on one or more of the specified columns of the table on the UPDATE operation:
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... END;
Let's assume that in one case, we want to maintain an audit experiment for each record that is inserted into the newly created COMPANY table (or, if it already exists, deleted and recreated):
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 the audit experiment going, we'll create a new table called AUDIT. Whenever there is a new record item in the COMPANY table, the log message is inserted into it:
sqlite> CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
Here, the ID is the ID of the AUDIT record, EMP_ID is the ID from the COMPANY table, and DATE maintains the timestamp of the record in THE COMPANY when it was created. So now let's create a trigger on the 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;
Now we'll start inserting records into the COMPANY table, which will result in an audit log record creating in the AUDIT table. So let's create a record in the COMPANY table, as follows:
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
This creates a record in the COMPANY table as follows:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
At the same time, a record is created in the AUDIT table. T his record is the result of a trigger, which we created on the INSERT operation on the COMPANY table. Similarly, triggers can be created on UPDATE and DELETE operations as needed.
EMP_ID ENTRY_DATE ---------- ------------------- 1 2013-04-05 06:26:00
You can sqlite_master triggers from the table, as follows:
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
The SQLite statement above lists only one entry, as follows:
name ---------- audit_log
If you want to list triggers on a particular table, connect the table name with the AND clause, as follows:
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';
The SQLite statement above lists only one entry, as follows:
name ---------- audit_log
Here's the DROP command that you can use to remove an existing trigger:
sqlite> DROP TRIGGER trigger_name;