Skip to main content

SQLite触发器:你不想错过的终极指南

什么是SQLite触发器

SQLite触发器是一个命名的数据库对象,在对关联表发出INSERT、UPDATE或DELETE语句时自动执行。

我们什么时候需要SQLite触发器

您经常使用触发器来启用复杂的审核。例如,您希望在敏感数据(如薪资和地址)发生变化时记录其变化。

此外,还可以使用触发器在数据库级别集中执行复杂的业务规则,并防止无效事务。

SQLite创建触发器语句

要在SQLite中创建新触发器,请使用create trigger语句,如下所示:

CREATE TRIGGER [IF NOT EXISTS] trigger_name 
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[WHEN condition]
BEGIN
statements;
END;

在这种语法中:

如果将触发时间和导致触发的事件结合起来,则总共有9种可能性:

假设使用UPDATE语句更新表中的10行,则会触发与该表关联的触发器10次。每个行触发器都会调用此触发器。如果与该表关联的触发器被触发一次,我们将该触发器称为FOR EACH语句触发器。

从3.9.2版开始,SQLite只支持每行触发器。它还不支持FOR-EACH语句触发器。

如果在WHEN子句中使用条件,则仅当条件为true时才会调用触发器。如果省略WHEN子句,将对所有行执行触发器。

请注意,如果删除一个表,所有关联的触发器也将被删除。但是,如果触发器引用了其他表,则如果删除或更新了其他表,则不会删除或更改触发器。

例如,触发器引用一个名为people的表,如果删除或重命名people表,则需要手动更改触发器的定义。

您可以使用以下格式的新旧引用来访问正在插入、删除或更新的行的数据:OLD。列名称和新名称。列名。

根据触发触发器的事件,可以使用新旧引用。

下表说明了这些规则:

SQLite触发器示例

让我们创建一个名为leads的新表来存储公司的所有业务Lead。

CREATE TABLE leads (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
phone text NOT NULL,
email text NOT NULL,
source text NOT NULL
);

1) SQLite之前插入触发器示例

假设您想在将新潜在客户插入潜在客户表之前验证电子邮件地址。在这种情况下,可以使用插入前触发器。

首先,创建一个BEFORE INSERT触发器,如下所示:

CREATE TRIGGER validate_email_before_insert_leads 
BEFORE INSERT ON leads
BEGIN
SELECT
CASE
WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
RAISE (ABORT,'Invalid email address')
END;
END;

我们使用新的引用来访问正在插入的行的电子邮件列。

为了验证电子邮件,我们使用LIKE操作符根据电子邮件模式确定电子邮件是否有效。如果电子邮件无效,RAISE函数将中止插入并发出错误消息。

其次,在leads表中插入一行包含无效电子邮件的内容。

INSERT INTO leads (first_name,last_name,email,phone)
VALUES('John','Doe','jjj','4089009334');

SQLite发出错误:“无效的电子邮件地址”,并中止了插入的执行。

第三,插入带有有效电子邮件的行。

INSERT INTO leads (first_name, last_name, email, phone)
VALUES ('John', 'Doe', 'john.doe@sqlitetutorial.net', '4089009334');

由于电子邮件有效,insert语句已成功执行。

SELECT
first_name,
last_name,
email,
phone
FROM
leads;

SQLite TRIGGER Leads Table

2) SQLite更新后触发器示例

潜在客户的电话和电子邮件非常重要,你不能丢失这些信息。例如,有人意外地将电子邮件或电话更新到错误的电子邮件或电话,甚至将其删除。

为了保护这些有价值的数据,您可以使用触发器记录对手机和电子邮件所做的所有更改。

首先,创建一个名为lead_logs的新表来存储历史数据。

CREATE TABLE lead_logs (
id INTEGER PRIMARY KEY,
old_id int,
new_id int,
old_phone text,
new_phone text,
old_email text,
new_email text,
user_action text,
created_at text
);

其次,创建一个更新后触发器,以便在电子邮件或电话列中有更新时将数据记录到lead_logs表中。

CREATE TRIGGER log_contact_after_update 
AFTER UPDATE ON leads
WHEN old.phone <> new.phone
OR old.email <> new.email
BEGIN
INSERT INTO lead_logs (
old_id,
new_id,
old_phone,
new_phone,
old_email,
new_email,
user_action,
created_at
)
VALUES
(
old.id,
new.id,
old.phone,
new.phone,
old.email,
new.email,
'UPDATE',
DATETIME('NOW')
) ;
END;

您注意到,在WHEN子句中的条件中,指定只有在email或phone列发生更改时才会调用触发器。

第三,将John的姓从Doe更新为Smith。

UPDATE leads
SET
last_name = 'Smith'
WHERE
id = 1;

更新后的触发日志\u contact\u未被调用,因为电子邮件或电话没有更改。

第四,将John的电子邮件和电话更新为新的。

UPDATE leads
SET
phone = '4089998888',
email = 'john.smith@sqlitetutorial.net'
WHERE
id = 1;

如果您查看日志表,您将看到其中有一个新条目。

SELECT
old_phone,
new_phone,
old_email,
new_email,
user_action
FROM
lead_logs;

SQLite TRIGGER After Update Trigger Example

您可以开发AFTER INSERT和AFTER DELETE触发器,将数据作为练习记录在lead_logs表中。

SQLite DROP触发器语句

要删除现有触发器,请使用drop trigger语句,如下所示:

DROP TRIGGER [IF EXISTS] trigger_name;

在这种语法中:

请注意,如果删除一个表,SQLite将自动删除与该表关联的所有触发器。

例如,要在插入线索触发之前删除validateemail,请使用以下语句:

DROP TRIGGER validate_email_before_insert_leads;

在本教程中,我们向您介绍了SQLite触发器,并向您展示了如何从数据库中创建和删除触发器。