从SQLite触发器中止以下语句 [英] Abort following statement from SQLite Triggers

问题描述

我想创建一个 SQLite触发器，类似于此触发器：

I want to create a SQLite trigger similar to this one:

CREATE TRIGGER "prevent_duplicate_emails"
BEFORE INSERT OF "email" ON "users"
FOR EACH ROW
BEGIN
    SELECT COUNT("email") FROM "users" WHERE "email" LIKE NEW."email"

    # FIX ME!
    IF COUNT("email") > 0:
        RAISE ABORT|FAIL, NEW."email" || ' already exists'
    ELSE:
        INSERT INTO "unique_emails" VALUES (NEW."email");
    ENDIF;
END;

所以基本上当我这样做时：

So basically when I do:

INSERT INTO "users" ("email") VALUES ("[e-mail]"); # WORKS
INSERT INTO "users" ("email") VALUES ("[e-mail]"); # FAILS
INSERT INTO "users" ("email") VALUES ("[e-mail]"); # WORKS

第一个 INSERT 将产生两个新记录（在表 users 和 unique_emails 中），而第二个记录 INSERT 将失败并且根本不产生任何记录。

The first INSERT will originate two new records (in tables users and unique_emails), while the second INSERT will fail and originate no records at all. Is it possible to do this from a trigger?

我阅读了文档，但是除了没有完全理解 RAISE 之外，

I read the docs, but besides not fully understanding RAISE I don't see a way to achieve this.

PS ：我知道我可以采用其他几种方法来获得唯一列表电子邮件，但这不是这个问题的重点。如果我可以在触发器内执行此检查/中止操作，那真的可以简化事情。

PS: I know I could take several other approaches in order to get a list of unique emails, but that's not the point of this question. It would really simplify things if I could do this check / abort from within the trigger.

推荐答案

找到了答案：

CREATE TRIGGER IF NOT EXISTS "prevent_duplicate_emails"
BEFORE INSERT OF "email" ON "users"
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Email already exists.')
    WHERE EXISTS (SELECT 1 FROM "unique_emails"
                  WHERE "email" LIKE NEW."email");
    INSERT INTO "unique_emails" VALUES (NEW."email");
END;

这篇关于从SQLite触发器中止以下语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！