SELECT RAISE(FAIL, 'because ' || table.column) FROM table;

(1.1) By rbroders on 2022-09-15 20:37:00 edited from 1.0 [link] [source]

I am using a BEFORE INSERT trigger to validate my data.

The RAISE function does not seem to allow table.column references.

Is there any other way to make this work? I could really improve the message if I was allowed to reference data from the table in the query.

Thanks -- Bob

P.S. Version 3.37.2

P.P.S I have an identical copy of my trigger on BEFORE UPDATE. It would be nice if the trigger could be CREATE TRIGGER xxx BEFORE INSERT, UPDATE ON table like some other databases support. Pretty please...

[Edited " -> ' and + ->

]

(2) By anonymous on 2022-09-15 04:22:55 in reply to 1.0 [link] [source]

You have access to NEW.column in before insert triggers.

(3) By Gunter Hick (gunter_hick) on 2022-09-15 07:28:18 in reply to 1.0 [source]

SELECT RAISE(FAIL, 'because ' || NEW.column);

Note that single quotes denote strings, whereas double quotes desginate identifiers; also, || is the concatenate operator, whereas + is the arithmetic addition operator; and a CAST(NEW.column AS TEXT) is implicitly performed if NEW.column is not already TEXT.

(4) By Mark Lawrence (mark) on 2022-09-15 08:01:49 in reply to 3 [link] [source]

I don't think you tested that all the way through:

    create table t(id integer primary key);
    create trigger bi_t before insert on t
    for each row begin
        select raise(fail, 'because ' || 'reason');
    end;
    insert into t values(1);

    -- Parse error near line 2: near "||": syntax error
    --   select raise(fail, 'because ' || 'reason');     select rai
    --                   error here ---^

The unfortunate issue is that the raise function is (unexpectedly?) defined as accepting an "error message" which must apparently be a fixed single string.

Its usefulness would be greatly enhanded if it accepted an SQL expression like most other functions, but I raised (ha ha) that issue a while ago without any response from the devs.

(5) By Mark Lawrence (mark) on 2022-09-15 08:05:36 in reply to 3 [link] [source]

My trigger example was overblown. Just running a simple SELECT RAISE(FAIL,'some'||'thing') naturally produces the same.

(6) By ddevienne on 2022-09-15 08:14:15 in reply to 4 [link] [source]

Its usefulness would be greatly enhanced if it accepted an SQL expression like most other functions

Although there could be a valid technical reason Richard made that choice.
If that's the case, I'd be curious to know why. Otherwise, enhancing raise() would be welcome indeed.

(7) By anonymous on 2022-09-15 08:33:45 in reply to 5 [link] [source]

Actually RAISE needs to run within a trigger, so your example stands. Your new one, while still produces the same error, will not function even if RAISE is enhanced to accept expressions

(8) By rbroders on 2022-09-15 20:44:21 in reply to 1.1 [link] [source]

Here is the full trigger I would like:

CREATE TRIGGER playerA1_already_playing_i BEFORE INSERT ON tournamentRoundMatches
BEGIN
SELECT RAISE(FAIL, p.lastName || ', ' || p.firstName || ' is already playing on ' || tc.location || ', ' || tc.courtNum || ' this round')
  FROM tournamentRoundMatches trm, players p, courts c
 WHERE tournamentID = NEW.tournamentID AND round = NEW.round AND courtID != NEW.courtID AND courtID = c.ID AND NEW.playerA1ID = p.ID
   AND NEW.playerA1ID IN (playerA1ID, playerA2ID, playerB1ID, playerB2ID);
END;

Hopefully this syntax is correct (hard to debug when stopped cold by column reference problem).  As you can see I need to join my foreign keys to get a decent message to the end-user.  Hopefully the devs will consider improving this soon.

Thanks --Bob

(9) By Matt Toschlog (toschlog) on 2024-05-07 23:23:55 in reply to 8 [link] [source]

+1 on this. I would really like to be able to include information from the table in my error message. My desired code is something like this:

CREATE TRIGGER check_type_before_update BEFORE UPDATE ON data WHEN NEW.typeName <> OLD.typeName BEGIN SELECT RAISE(ABORT, 'TypeMismatch:' || OLD.typeName); END;

(10) By anonymous on 2024-05-11 19:15:15 in reply to 1.1 [link] [source]

Other Anonymous...

Just found and tested this check-in

Yes!! That is something I can really use. Any chance of this getting in the next release?

(11) By Richard Hipp (drh) on 2024-05-11 19:48:07 in reply to 10 [link] [source]

We are pencils-down for 3.46.0, so "no". That's why it is on a branch. It will go into 3.47.0 at the earliest.

(12) By anonymous on 2024-05-11 21:43:23 in reply to 11 [link] [source]

Too bad, but understandable and not unexpected.

Since I'm allready building from source I will probably be cherrypicking this and other later fixes while waiting for 3.47.