9

I try to create an instead-of trigger for an update on a table. The normal use of instead-of triggers are views but the Sqlite manual says that instead-of triggers are also valid for tables. But I get the error: cannot create INSTEAD OF trigger on table. And I am wondering why.

I use foreign keys:

PRAGMA foreign_keys = ON;

And I have two tables. An identifier table:

CREATE TABLE id
(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    created REAL    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

And a table which references the identifiers:

CREATE TABLE person
(
    id        INTEGER NOT NULL DEFAULT (last_insert_rowid()) REFERENCES id,
    login     TEXT,
    password  TEXT,
    firstname TEXT,
    lastname  TEXT,
    email     TEXT,
    created   REAL  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created)
);

Insert works fine:

INSERT INTO id DEFAULT VALUES;
INSERT INTO person (login) VALUES ('root');
SELECT * FROM person;
1|root|||||2012-02-28 18:03:45

Now I want to define the following trigger, which converts an update into an insert:

CREATE TRIGGER person_update INSTEAD OF UPDATE OF login, password, firstname, lastname, email ON person
BEGIN
    INSERT INTO person (login, password, firstname, lastname, email)
    VALUES (new.login, new.password, new.firstname, new.lastname, new.email);
END;

But it fails with the above error and I do not understand why.

3 Answers 3

14

I tried going through the source code (search for "cannot create INSTEAD OF") and despite reading your link in the comment where the create trigger statement is outlined in great detail, I would say that triggers with INSTEAD OF are only for views and cannot be used with tables.

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

3 Comments

Yes, this is the right answer. INSTEAD OF triggers are just for views.
Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement.
@NoobSaibot The "bullshit" is written in the official Sqlite documentation.
0

I'm hoping that this is just a typo in the post, but your create statement should read:

INSTEAD OF UPDATE ON

you've typed:

INSTEAD OF UPDATE OF 

1 Comment

No this is wrong. The syntax is INSTEAD OF UPDATE OF column-name ON table-name. See here: sqlite.org/lang_createtrigger.html
0

Just create a view person_view as select * from person and point your trigger to it.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.