1.33. SQLite trigger (Trigger)

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;