On 2015-04-01 09:09 PM, Gert Van Assche wrote:
> Dr. Hipp, thanks for the tip. I put
> .bail on
> in the script.
>
>
> Ryan, I think I don't know how to trigger the bail out from within a SELECT
> statement.
> I tried
> SELECT CASE (select count(*) from T1) WHEN (select count(*) from T2)
> then 'OK' else RAISE(FAIL) END;
>
> But this is definitely not the right way to do it. If I understand the doc
> correctly, it should be an expression, but I don't see how I can do this...
On closer inspection, it seems the raise function is really only allowed
in triggers. So you can use a trigger.
AS a proof of concept I've made a table like this:
CREATE TABLE "vChk" ("CheckTS" NUMERIC DEFAULT (CURRENT_TIMESTAMP));
CREATE TRIGGER Trig_vChk_T1_T2 AFTER INSERT ON vChk FOR EACH ROW
BEGIN
SELECT (CASE (select count(*) from T1) WHEN (select count(*) from T2)
THEN 'OK' ELSE RAISE(FAIL,'The number of Inserts are mismatched...') END);
END;
So then at the end of any insert bits of a script, you would simply add
a line like this:
INSERT INTO vChk DEFAULT VALUES;
and that would cause the script to stop execution and fail if the
statement requires it.
You can add any amount of triggers to that one vChk table to check all
sorts of things.
I've tested this time, it works.
