Sqlite 简明教程

SQLite - Triggers

SQLite Triggers 是数据库回调函数，当指定的数据库事件发生时自动执行/调用。以下是有关 SQLite 触发器的重要说明 -

SQLite Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. Following are the important points about SQLite triggers −

  1. SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on one or more specified columns of a table.

  2. At this time, SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence, explicitly specifying FOR EACH ROW is optional.

  3. Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted, or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with.

  4. If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

  5. The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification, or removal of the associated row.

  6. Triggers are automatically dropped when the table that they are associated with is dropped.

  7. The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename not database.tablename.

  8. A special SQL function RAISE() may be used within a trigger-program to raise an exception.

Syntax

这是创建 trigger 的基本语法。

Following is the basic syntax of creating a trigger.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

此处， event_name 可以在所述表 table_name 上是插入，删除和更新数据库操作。您可以在表名后选择指定 FOR EACH ROW。

Here, event_name could be INSERT, DELETE, and UPDATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.

以下是在表的某个或多个指定列上针对更新操作创建触发器的语法。

Following is the syntax for creating a trigger on an UPDATE operation on one or more specified columns of a table.

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

Example

让我们考虑这种情况，我们希望保留表 COMPANY 中每条记录的审核记录，我们如下新建表 (如果您已经拥有 COMPANY 表，请删除它)。

Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we create newly as follows (Drop COMPANY table if you already 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
);

要保留审核记录，我们将创建一个名为 AUDIT 的新表，其中会在 COMPANY 表中出现新记录的任何时候插入日志消息。

To keep audit trial, we will create a new table called AUDIT where the log messages will be inserted, whenever there is an entry in COMPANY table for a new record.

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

此处，ID 是 AUDIT 记录 ID，EMP_ID 是自 COMPANY 表而来的 ID，DATE 将会在 COMPANY 表中创建记录时保留时间戳。现在，让我们如下创建 COMPANY 表上的触发器 −

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 created in COMPANY table. Now let’s create 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;

现在，我们将开始实际工作，让我们开始在 COMPANY 表中插入记录，该记录应该导致在 AUDIT 表中创建一个审核记录。如下在 COMPANY 表中创建一个记录 −

Now, we will start actual work, Let’s start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. Create one record in COMPANY table as follows −

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

这将在 COMPANY 表中创建一个如下所示的记录 −

This will create one record in COMPANY table, which is as follows −

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

同时，将在 AUDIT 表中创建一个记录。此记录是在 COMPANY 表中针对插入操作创建的触发器的结果。同样，您可以根据要求针对更新和删除操作创建触发器。

Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation in COMPANY table. Similarly, you can create your triggers on UPDATE and DELETE operations based on your requirements.

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

Listing Triggers

如下可以在 sqlite_master 表中列出所有触发器 −

You can list down all the triggers from sqlite_master table as follows −

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

上述 SQLite 语句将仅如下列出一个条目 −

The above SQLite statement will list down only one entry as follows −

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

如果您想列出特定表上的触发器，请使用 AND 子句与表名一起使用，如下所示 −

If you want to list down triggers on a particular table, then use AND clause with table name as follows −

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

上述 SQLite 语句也将仅如下列出一个条目 −

The above SQLite statement will also list down only one entry as follows −

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

Dropping Triggers

以下是 DROP 命令，它可用来删除现有触发器。

Following is the DROP command, which can be used to drop an existing trigger.

sqlite> DROP TRIGGER trigger_name;