SQLite Forum

What is the point of a lone SELECT statement in an SQLite trigger?
Login

What is the point of a lone SELECT statement in an SQLite trigger?

(1) By dorian on 2021-03-30 07:34:49 [source]

Hi,

I'm reading the CREATE TRIGGER documentation and the syntax diagram says that you can put a SELECT statement in a trigger all by itself. Why would you do that?

From what I understand there is no imperative language in trigger bodies and you can't SELECT INTO anything. The only use case I can think of is something like SELECT RAISE(ABORT, 'bad condition') WHERE bad_condition; — are there any others?

Thanks,

(2) By anonymous on 2021-03-30 13:57:48 in reply to 1 [link] [source]

Well here is an example why you want to something like that:
CREATE TRIGGER IF NOT EXISTS LOC_CHECK_DATA BEFORE INSERT ON LOCATION
WHEN (NEW.LOC_OBJECT_TYPE < 60)
BEGIN

    SELECT CASE 1
        WHEN NEW.LOC_DTS_CREATED != NULL THEN RAISE (ABORT, 'Table LOCATION - INSERT LOC_DTS_CREATED: ABORT: Setting the DTS externally is not allowed')
        WHEN NEW.LOC_DTS_UPDATED != NULL THEN RAISE (ABORT, 'Table LOCATION - INSERT LOC_DTS_UPDATED: ABORT: Setting the DTS externally is not allowed')
        END;

    -- v0.20.01 Allow components without an object type and object sub type
    -- These Objects must have an UIC that starts with 1 followed by 6 digits, have no IBNR number and no DS100 code
    SELECT CASE (NEW.lOC_OBJECT_TYPE == 0)
	WHEN 1
		THEN CASE 1
			WHEN (NEW.LOC_OBJECT_SUB_TYPE == 0)
				--THEN CASE (((SELECT F_FLAG FROM FLAGS WHERE F_INDEX == 4) | 2) - ((SELECT F_FLAG FROM FLAGS WHERE F_INDEX == 4) & 2))
				THEN CASE FS(4)
					-- If the flag is set then the XOR result will be 0
					WHEN 1
						THEN CASE 1
							WHEN (NEW.LOC_UIC REGEXP '^1([0-9]{6})$')
								THEN RAISE (ABORT, 'Table LOCATION (INSERT): ERROR 54-50: Items without an object type must have a UIC that starts with 1 followed by 6 digits')
							WHEN NOT (NEW.LOC_IBNR ISNULL)
								THEN RAISE (ABORT, 'Table LOCATION (INSERT): ERROR 54-51: Items without an object type have no IBNR number')
							WHEN NOT (NEW.lOC_DS100 ISNULL)
								THEN RAISE (ABORT, 'Table LOCATION (INSERT): ERROR 54-52: Items without an object type have no DS100 code')
							WHEN NOT (NEW.LOC_STATION_NAME REGEXP '^NO-1([0-9]{6})$')
								THEN RAISE (ABORT, 'Table LOCATIOPN (INSERT): ERROR 54-53: Items without an object type have a station name that starts with NO-1 followed by 6 digits')
						END
					WHEN 0
						THEN RAISE (ABORT, 'Table LOCATION (INSERT): ERROR 54-54: Items wihout an object type are not allowed')
				END
			WHEN NEW.LOC_OBJECT_SUB_TYPE != 0
				THEN RAISE (ABORT, 'Table LOCATION (INSERT): ERROR 54-55: Items without an object type must have an object subject type equal to 0')
			END
	END;

(7) By Mark Lawrence (mark) on 2021-03-31 05:55:50 in reply to 2 [link] [source]

You might want to read up on comparing values against NULL. Your first comparison:

WHEN NEW.LOC_DTS_CREATED != NULL

Will never evaluate to true. You want to use something like this instead:

WHEN NEW.LOC_DTS_CREATED IS NOT NULL

(8) By anonymous on 2021-03-31 06:38:02 in reply to 7 [link] [source]

Good tip. And more SQL.
But there is something strange. I executed a couple of tests years ago, and it didn't fail or did I miss something then? It's time to test again.

(3) By Richard Hipp (drh) on 2021-03-30 14:30:47 in reply to 1 [link] [source]

You might use this to invoke application-defined functions that have side-effects that are outside the purview of SQL.

(4) By dorian on 2021-03-30 23:28:33 in reply to 3 [link] [source]

Ahh, of course—custom functions. It would be beneficial to mention a use case for SELECT in a trigger in the documentation; even conditionally invoking RAISE is enough of one on its own.

I have also discovered that UPDATE...FROM throws a syntax error in a trigger (SQLite 3.31.1 that ships with Ubuntu 20.04), but that does not appear to be in the documentation either, at least not in the section on syntax restrictions.

(5) By Keith Medcalf (kmedcalf) on 2021-03-30 23:48:28 in reply to 4 [link] [source]

UPDATE ... FROM ... requires version 3.33.0 or later.

(6) By dorian on 2021-03-31 04:42:19 in reply to 5 [link] [source]

Ah, good to know.

(9) By anonymous on 2021-03-31 06:46:46 in reply to 3 [link] [source]

FS and REGEXP are only available after loading extensions. Nice tip: XOR in SQL ((SELECT F_FLAG FROM FLAGS WHERE F_INDEX == 4) | 2) - ((SELECT F_FLAG FROM FLAGS WHERE F_INDEX == 4) & 2)