SQLite trigger
SQLite's trigger is a callback function of the database, which will automatically execute/call when a specified database event occurs. Here are the key points about SQLite’s triggers: SQLite Trigger (Trigger) is the callback function of the database, which will be automatically executed/called when the specified database event occurs. The following are the key points about SQLite's triggers:
SQLite's triggers can be specified to trigger when DELETE, INSERT or UPDATE occurs in a specific database table, or when Fires when one or more columns of the specified table are updated.
SQLite only supports FOR EACH ROW triggers (Triggers), not FOR EACH STATEMENT triggers (Triggers). Therefore, explicitly specifying FOR EACH ROW is optional.
WHEN clauses and trigger actions may be accessed using the forms NEW.column-name and OLD.column-name References an inserted, deleted, or updated row element, where column-name is the name of a column from the table associated with the trigger.
If a WHEN clause is provided, the SQL statement is executed only for the specified rows for which the WHEN clause is true. If no WHEN clause is provided, the SQL statement is executed for all rows.
The BEFORE or AFTER keyword determines when to execute the trigger action before or after the insertion, modification, or deletion of the associated row.
When the table associated with the trigger is deleted, the trigger (Trigger) is automatically deleted.
The table to be modified must exist in the same database as the table or view to which the trigger is attached, and only tablename must be used, not database.tablename.
A special SQL function RAISE() can be used to trigger exceptions thrown within the program.
Syntax
Create Trigger (Trigger)The basic syntax is as follows:
ON table_name
BEGIN
-- Trigger logic goes here....
END;
Here, event_name Can be INSERT, DELETE and UPDATE database operations on the mentioned table table_name. You can optionally specify FOR EACH ROW after the table name.
The following is the syntax for creating a trigger (Trigger) on one or more specified columns of the table on an UPDATE operation:
ON table_name
BEGIN
-- Trigger logic goes here....
END;
Example
Let us assume a situation where we want to maintain an audit test for every record inserted into the newly created COMPANY table (if it already exists, delete and recreate it):
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
To keep the audit experiment going, we will create a new table called AUDIT. Whenever there is a new record item in the COMPANY table, the log message will be inserted into it:
ENTRY_DATE TEXT NOT NULL
);
BEGIN
INSERT INTO AUDIT( EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
At the same time, a record will be created in the AUDIT table. This record is the result of the trigger we created on the COMPANY table on the INSERT operation. Similarly, triggers can be created on UPDATE and DELETE operations as needed.
---------- ------------------------
1 2013-04 -05 06:26:00
List triggers (TRIGGERS)
You can list all triggers from the sqlite_master table as shown below ：
WHERE type = 'trigger';
The above SQLite statement will only list one entry, as follows:
----------
audit_log
Use AND if you want to list triggers on a specific table The clause connects the table name as follows:
WHERE type = 'trigger' AND tbl_name = 'COMPANY';
The above SQLite statement will only list one entry, as follows:
----------
audit_log
Delete Triggers (TRIGGERS)
The following is the DROP command, which can be used to delete existing triggers:
