0

I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during a certain period of time).

I have a database History and in this database exists the table events with the following schema:

CREATE TABLE events
    (
     id integer primary key autoincrement, 
     msg varchar(20) NOT NULL, 
     ago varchar(30) NOT NULL
    );

I know a mySQL implementation would likely use INSTEAD OF INSERT on the table, though I also know INSTEAD OF can't be executed on tables, only views in sqlite. (To be honest I'm new to databases so I only kinda understand this concept).

Would anyone with some more database experience be able to help me with writing (or at the very least understanding the theory behind) a trigger that does not allow data to be inserted to the table?

Edit: This article is where I first got the idea that this would be possible

4
  • I'd also be interested in the feasibility of sending a BEGIN TRANSACTION, allowing an INSERT, and then performing a ROLLBACK after Jul 28, 2020 at 14:05
  • Have you looked at using a BEFORE trigger like in this example? You could simply raise an error on ANY insert.
    – AMtwo
    Jul 28, 2020 at 14:18
  • Interesting solution, do you know if the warning message would be suppressible? Jul 28, 2020 at 14:20
  • @AMtwo Actually, that's something I can figure out on my own. But your comment solved my problem so if you want to answer I'll accept it Jul 28, 2020 at 14:29

1 Answer 1

-1

It will be something like

CREATE TRIGGER forbid_events_update
INSTEAD OF UPDATE
ON events
FOR EACH ROW
BEGIN
    RAISE(ROLLBACK, 'Events update not allowed'); /* maybe ABORT of FAIL */
END;

And the same for INSTEAD OF DELETE.

1
  • 1
    @mustaccio I have removed the phrase "If your database engine does not allow INSTEAD OF triggers on tables, you can use BEFORE triggers for the same purpose." which you have added into the answer. The question is tagged as SQLite-relative, so INSTEAD OF triggers are available.
    – Akina
    Jul 29, 2020 at 4:46

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.