SQLITE: stop execution if select returns specific value

Question

Is there any way to write an SQL input file for sqlite that would somehow "throw" an error, eg. exited the transaction with rollback, if a condition isn't met?

I have a script that is supposed to do something, but only if there is a certain row in one table. If it's not there, the execution of the script might have fatal results and corrupt the db.

The script is only started on demand right now, but I would prefer to add a fail-safe which would prevent its execution in case there is some issue.

Basically what I need is something like

/* IF */ SELECT value FROM meta WHERE key = 'version' /* != hardcoded_version_string THROW SOME EXCEPTION */

Is there any way to accomplish that? In Postgre / Oracle this could be done using PLSQL but I am not sure if sqlite support any such a thing?

Answer 1

Triggers can use the RAISE function to generate errors:

CREATE VIEW test AS SELECT NULL AS value;
CREATE TRIGGER test_insert
INSTEAD OF INSERT ON test
BEGIN
  SELECT RAISE(FAIL, 'wrong value')
  WHERE NEW.value != 'fixed_value';
END;

INSERT INTO test SELECT 'fixed_value';
INSERT INTO test SELECT 'whatever';
Error: wrong value

Answer 2

Is there any way to write an SQL input file for sqlite that would somehow "throw" an error, eg. exited the transaction with rollback, if a condition isn't met?

One workaround may be to create dummy table and explicitly violate NULL constraint:

CREATE TABLE meta("key" VARCHAR(100));
INSERT INTO meta("key") VALUES ('version');

CREATE TEMPORARY TABLE dummy(col INT NOT NULL);

Transaction:

BEGIN TRANSACTION;

INSERT INTO dummy(col)
SELECT NULL                    -- explicit insert of NULL
FROM meta  
WHERE "key" = 'version';
-- Error: NOT NULL constraint failed: dummy.col

-- rest code
INSERT INTO meta("key")
VALUES ('val1');

INSERT INTO meta("key")
VALUES ('val2');
-- ...

COMMIT;

SqlFiddleDemo

Keep in mind that SQLite is not procedural language and this solution is a bit ugly.