Why does SQLite give Cannot create instead of trigger on table

Question

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.

CL. · Accepted Answer · 2015-05-19 11:28:33Z

9

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.)

answered May 19, 2015 at 11:28

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steve Waring Over a year ago

Not very helpful documentation. I looked for a link to report documentation errors but there was none. I'll just code the test against the table.

Paul Calabro Over a year ago

Ahhhhh...now it makes sense! Thx!

Paul Calabro Over a year ago

Any idea why INSTEAD OF is only for views?

iwis Over a year ago

@PaulCalabro: It is defined in the SQL Standard: "If INSTEAD OF is specified, then T shall be a viewed table. ... Otherwise, T shall be a base table ..." The viewed table means a view. The base table means a normal table. INSTEAD OF can be declared only on views also in Oracle and PostgreSQL.

Artem Odnovolov · Accepted Answer · 2019-09-06 21:04:00Z

1

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;

answered Sep 6, 2019 at 21:04

Artem Odnovolov

1211 silver badge4 bronze badges

1 Comment

Steve Waring Over a year ago

Yes, that looks like it would work. I no longer have SQLite installed so I can't test it. For completeness, SELECT RAISE(IGNORE) would cause any subsequent triggers to be ignored, but in my original scenario, there were none.

Froeen · Accepted Answer · 2020-04-06 08:30:39Z

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;

Collectives™ on Stack Overflow

Why does SQLite give Cannot create instead of trigger on table

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related