1.33. SQLite trigger (Trigger)

SQLite 触发器（Trigger） Is a callback function for the database that is automatically executed / called when a specified database event occurs. Here are the main points about SQLite’s trigger (Trigger):

The trigger (Trigger) of SQLite can be specified to occur in a specific database table DELETE 、 INSERT Or UPDATE Or when one or more columns of the specified table are updated.
SQLite supports only FOR EACH ROW triggers (Trigger), not FOR EACH STATEMENT triggers (Trigger). Therefore, explicitly specifying FOR EACH ROW is optional.
WHEN Clauses and Trigger actions may access the usage form NEW.column-name And OLD.column-name Where column-name is the name of the column of the table associated with the trigger
If provided WHEN Clause, then only for WHEN Clause is true, the specified line executes the SQL statement. If it is not provided WHEN Clause, execute for all rows SQL Statement.
BEFORE Or AFTER Keyword determines when to execute the trigger action, and determines whether to perform the trigger action before or after the insertion, modification, or deletion of the associated row.
Automatically delete the trigger (Trigger) when the table associated with the trigger is deleted.
The table to be modified must exist in the same database, the table or view that is attached as a trigger, and must only use the tablename Instead of database.tablename .
A special SQL Function RAISE() Can be used to throw an exception within a trigger program.

1.33.1. Grammar ¶

Create 触发器（Trigger） The basic syntax is as follows:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- 触发器逻辑....
END;

Here, event_name It can be in the table mentioned. table_name On INSERT、DELETE 和 UPDATE Database operation. You can choose to specify FOR EACH ROW after the table name.

The following is in UPDATE The syntax for creating a trigger (Trigger) on one or more specified columns of a table on the

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
 -- 触发器逻辑....
END;

1.33.2. Example ¶

Let’s assume a situation in which we want to be inserted into the newly created COMPANY Each record-keeping audit experiment in the table (delete and recreate if it already exists):

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To maintain the audit experiment, we will create a file named AUDIT It’s a new watch. Whenever COMPANY When there is a new entry in the table, the log message is inserted into it:

sqlite> CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

Here, ID is AUDIT The recorded ID, EMP_ ID ``, is from ``COMPANY Tabular ID , DATE will be maintained COMPANY The timestamp when the record was created in the. So, for now, let’s COMPANY Create a trigger on the 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’re going to start COMPANY Insert a record into the table, which will result in a AUDIT Create an audit log record in the table. So, let’s do it in COMPANY Create a record in the table as follows:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

This will be in the COMPANY Create the following record in the table:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

At the same time, there will be AUDIT Create a record in the table. This record is the result of the trigger, and this is what we did in COMPANY On the watch INSERT The trigger (Trigger) created on the operation. Similarly, you can use the UPDATE And DELETE Create a trigger (Trigger) on the operation.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

1.33.3. List triggers (TRIGGERS) ¶

You can start from the sqlite_master All triggers are listed in the table, as follows:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

The above SQLite statement lists only one entry, as follows:

name
----------
audit_log

If you want to list triggers on a particular table, use the AND Clause to connect the table name, as follows:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

Above. SQLite Statement lists only one entry, as follows:

name
----------
audit_log

1.33.4. Delete trigger (TRIGGERS) ¶

The following is DROP Command that can be used to delete existing triggers:

sqlite> DROP TRIGGER trigger_name;