CODEWITHSUNDEEP
triggerssqliteandroid-sqlite
Steve Waring
Steve Waring

Reputation: 3023

Why does SQLite give Cannot create instead of trigger on table

With this table

create table FOLDER
(
    _ID integer primary key autoincrement,
    NAME text not null,
    PARENT integer not null,
    DELETED integer,
    constraint VALUEOF_folder_deleted check (DELETED == 1 or DELETED isnull) on conflict abort,
    unique (NAME, PARENT) on conflict abort
);

I wanted to replace an insert where the NAME PARENT combination exists and DELETED is set to 1 with a replace setting the DELETED field to null.

I tried this trigger:

CREATE TRIGGER REPLACE_INS_folder instead of insert on FOLDER
when exists (select 1 from FOLDER where NAME == new.NAME and PARENT == new.PARENT and DELETED == 1)
begin
    update FOLDER set DELETED = null where NAME == new.NAME and PARENT == new.PARENT;
end;

but received: Error: cannot create INSTEAD OF trigger on table: FOLDER

Initially I had a syntax error in the trigger, but received the same error, suggesting that there is something more restrictive going on. The diagram in this documentation https://www.sqlite.org/lang_createtrigger.html suggests my trigger is valid. However, the second part of the text A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table. makes me wonder if WHEN is only allowed with update triggers.

Note this is a trigger on a table, not on a view.

Upvotes: 2

Views: 2209

Answers (3)

Froeen
Froeen

Reputation: 11

Since SQLite 3.24.0 (2018-06-04), the requested functionality is elegantly encapsulated in the UPSERT scheme INSERT..ON CONFLICT: https://www.sqlite.org/lang_UPSERT.html

However, in my case, the "ON CONFLICT" clause is not recognized by the programming language I use to write the database client.

So I have implemented a modified version of Artem Odnovolov's suggestion. It works, but it needed some adaptation to my scheme which is a little more general than the one posted by Steve Waring. The generalized version is:

  • Use "INSERT OR IGNORE" instead of "INSERT" to insert new rows in the table. This supersedes the SELECT RAISE(IGNORE) statement. I.e. subsequent triggers are not ignored.
  • Replacing "==" by "IS" allows for comparison of parameters that may be NULL.
  • Checking the value of OLD.deleted against NEW.deleted alows for a more general UPSERT behavior.

Still using Steve Waring's table as example, the table creation code would then look like:

CREATE TABLE folder(id      integer PRIMARY KEY,
                    name    text,
                    parent  integer,
                    deleted integer,
                    UNIQUE(name, parent)
                   );

And the BEFORE INSERT trigger:

    CREATE TRIGGER replace_ins_folder
    BEFORE INSERT ON folder

    WHEN EXISTS (SELECT 1 FROM folder       --check for violation of UNIQUE constraint
                 WHERE name IS NEW.name
                   AND parent IS NEW.parent
                )
    BEGIN
        UPDATE folder
        SET deleted = NEW.deleted
        WHERE name IS NEW.name 
          AND parent IS NEW.parent 
          AND deleted IS NOT NEW.deleted; -- only do update when there is something to change
                                          -- otherwise subsequent ON UPDATE triggers may fire when not intended
    END;

Upvotes: 1

Artem Odnovolov
Artem Odnovolov

Reputation: 121

Try this:

CREATE TRIGGER replace_ins_folder
BEFORE INSERT ON folder
WHEN EXISTS (SELECT 1 FROM folder
             WHERE name == new.name
               AND parent == new.parent
               AND deleted == 1)
BEGIN
    UPDATE folder
    SET deleted = NULL
    WHERE name == new.name AND parent == new.parent;

    SELECT RAISE(IGNORE);
END;

Upvotes: 1

CL.
CL.

Reputation: 180060

The documentation says:

Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement.

This is rather misleading. What this sentence is actually trying to say is that

  • on normal tables, you can use only BEFORE and AFTER triggers, but
  • on views, you can use only INSTEAD OF triggers.

You cannot use INSTEAD OF triggers on a table. Change it into a view:

CREATE TABLE FolderTable(...);
CREATE VIEW Folder AS SELECT * FROM FolderTable;
CREATE TRIGGER ... INSTEAD OF INSERT ON Folder ...;

(You then also need INSTEAD OF UPDATE/DELETE triggers.)

Upvotes: 9

Related Questions