SQLite Forum

CTEs in triggers
Login

CTEs in triggers

(1) By Joshua Netterfield (jnetterf) on 2020-11-01 21:41:04 [link] [source]

The documentation states that:

Common table expression are not supported for statements inside of triggers.

However, I seem to be able to use CTEs in triggers in INSERT INTO statements so long as the CTE is nested. For example, the following silly code seems to work:

CREATE TABLE src(content);
CREATE TABLE dst(content);
CREATE TRIGGER copy_content INSERT ON src BEGIN
  INSERT INTO dst SELECT (
    WITH loophole AS (SELECT new.content)
    SELECT * FROM loophole
  );
END;
INSERT INTO src VALUES(1);

I would appreciate some clarification. Why are CTEs restricted in some cases? Will nesting CTEs in SELECT() in triggers cause terrible things to happen?

(2) By Larry Brasfield (LarryBrasfield) on 2020-11-02 00:56:43 in reply to 1 [link] [source]

The phrase "not supported" is a term of art meaning "may do something strange and undesirable under some conditions, may not perform consistently across versions, and should not be relied upon by code purported to be reliable." It need not lead to terrible consequences consistently, or ever depending upon the local meaning of "terrible". But when anybody uses unsupported features, they are choosing to forego any support for whatever results from such use.

You might buy some rappelling line and hardware rated for 5000 Newtons of tension strength, and find that the first 10 articles test to 3 times that. If you then decide to use it to lower yourself in your car down a cliff, you might live to tell about it. Or, if the line parts partway down, in your final few seconds you can tell yourself, "I guess nothing more than 5000 Newtons is 'supported'."

(3) By Joshua Netterfield (jnetterf) on 2020-11-02 01:37:49 in reply to 2 [link] [source]

If that is the case, perhaps the documentation should be reworded for clarity, as "not supported" can read "does not ever work". Indeed, WITH before an INSERT statement in a trigger results in a syntax error. I was surprised when the above workaround ran.

Is the restriction of CTEs in triggers due to difficulty parsing them or because it "may do something strange and undesirable under some conditions"? If so, which?

(7) By Richard Hipp (drh) on 2020-11-02 11:41:10 in reply to 3 [link] [source]

Is the restriction of CTEs in triggers due to difficulty parsing them

Yes. It's a lot more logic that has to be written, tested, debugged, and maintained for 30 years, and that is mostly unused. (You are the first person to complain that it is not present.)

(9) By Joshua Netterfield (jnetterf) on 2020-11-02 15:32:54 in reply to 7 [link] [source]

I appreciate your response and continue to be amazed by SQLite. No complaints, only confusion.

As this is a parsing issue, would it make sense to amend the documentation to state that CTEs in subqueries and SELECT expressions work, even in triggers?

(11) By miasuji on 2021-05-14 20:23:23 in reply to 7 [link] [source]

Just chiming in here in support of this feature. I'm making very heavy use of triggers for data modification, and have frequently wished I could use CTE's in them.

Usually there are 2 strategies to work around the restriction:

  1. Use the more verbose spelling. However, there are cases where this makes the query much harder to write, read, and maintain:

    • The verbose spelling is much more verbose, ie. you have to spell out the same long SELECT multiple times, and edit them each if you need to make changes.
    • The verbose spelling is "inside out" in a way that makes it difficult to write, read, and maintain. I usually have to write the CTE first, then refactor it to a nested SELECT.
  2. Use a named view. However, there are cases where being able to do WHERE x = NEW.x dramatically speeds up the query. In my case, this occurs when using window functions in more complicated CTE's that the optimization resulting from my earlier post doesn't handle.

(1) seems like the raison d'etre of non-recursive CTE's, and would be nice to have in triggers.

(2) seems like an implementation detail, though I'm not sure it's tractable to get the WHERE push-down optimization to work in all cases. In these cases, I can resort to the "inside-out" spelling, but again this is tedious for the reasons mentioned above.

In my case, I think it would be sufficient to make the above "nesting" trick explicitly supported, if that's easier than supporting top-level CTE's.

Disclaimer: None of this should be construed as a complaint. I continue to be in love with SQLite. Thanks! :-)

(12) By Anton Dyachenko (ahtoh_) on 2023-02-20 23:27:46 in reply to 7 [link] [source]

I disagree with the logical implication here "and that is mostly unused".

If nobody complainted yet about CTE in trigger that doesn't mean it is not needed or would be unused if it were implemented. I think having no people complained about CTE in triggers just means that people have tried to use CTE in triggers and found they don't work than checked documentation and saw that they are not supported == no sence to complain.

The application I work on is heavily relying on recursive CTEs and we have specific promise to our users - forward compatibility. By compatibility I mean at least not crashing when an older version of our app works with a newer version of database. Our application stores db on external drives that can be plugged into several devices with different versions and different OS/platforms of the app and we can't control when and if users will upgrade the app. Currently business logic is implemented in the app binaries and this is always a problem for forward compatibility whenever we need to amend the schema. So ideal for us solution would be moving logic into stored procedures but sqlite doesn't support them. The next obvious solution is to move most of the logic into triggers but the logic relies on recursive CTEs. As a workaround I tried to have views that implements those CTEs and use views in triggers but this performs significantly worse than CTEs. Having no CTE in triggers force me to keep significant amount of logic in the app binaries instead of triggers.

(13) By Domingo (mingodad) on 2023-02-21 08:12:09 in reply to 12 [link] [source]

Do you know that there is https://cgsql.dev/ ?

And you can also test it online here https://mingodad.github.io/CG-SQL-Lua-playground/ .

With it you can get a lot of stored procedure like functionality.

(8) By JFMcLuggage (mccon01) on 2020-11-02 12:22:22 in reply to 2 [link] [source]

Mr. Brasfeld's reply, made suitably generic, should be inserted in all technical documentation.

(4) By Keith Medcalf (kmedcalf) on 2020-11-02 02:03:54 in reply to 1 [link] [source]

Of course, the statement you have specified in the trigger (the INSERT statement), once the syntactic sugar is removed, is simply:

INSERT INTO dst SELECT new.content;

In all usage cases of the WITH statement, other than recursive usage, is merely "syntactic sugar" for alternate spellings of the same thing that does not use the WITH keyword.

In other words, there is no particular reason why a WITH statement will not WORK in a trigger, though if you use one and it happens to work today but not tomorrow then that is YOUR problem, not the SQLite3 authors problem. Nor is it their problem it you use one that you think should work but does not.

"Not supported" does not mean "does not work". In the more common case the phrase is a marketing term which means "make money from". That is, when Microsoft says "Windows XP is no longer supported", that has nothing whatsoever to do with "working" but rather with the speaker "making money from" whatever they are disavowing. That is, Microsoft is saying that "Microsoft no longer makes money from Windows XP" and that you should give them money to transition to a product which they do "make money from" on an ongoing basis.

In the case of SQLite3, this usage makes no sense. Rather it means that if it fails to work as YOU intend, then that is YOUR problem, not MINE.

It is merely an advice that if you desire "guaranteed working" you should explore alternate spellings which achieve the same objective result but do not use the WITH syntax because although it may appear to work today, tomorrow that might not be the case and that no "special effort" will be made to ensure that it does work.

(5) By doug (doug9forester) on 2020-11-02 03:19:51 in reply to 4 [link] [source]

I disagree vigorously with the implementation of Sqlite which allows one to use SQL constructs which are "not supported". I think SQL is sufficiently complex that I should not have to pull out a manual (or search deeply) every time I use a construct. The parser/interpreter should surface an error when a "not supported" construct is used. To say it's on me to know everything there is to know about Sqlite's version of SQL, is wrong.

(6) By Keith Medcalf (kmedcalf) on 2020-11-02 06:09:45 in reply to 5 [source]

Be that as it may, the following are identical semantics with different spellings, yet one is an error and the other is not.

sqlite> create trigger xx after insert on x begin
   ...> with t as (select * from new) insert into y select x from t;
   ...> end;
Error: near "insert": syntax error
sqlite> create trigger xx after insert on x begin
   ...> insert into y select x from (select * from new);
   ...> end;
sqlite>

So you are saying that the use of WITH (CTE's) where they are "rejected" are not supported but uses where they are not rejected are supported, despite documentation to the contrary?

And that you would have (somehow) a different expectation because the following spelling is not rejected:

sqlite> create trigger xx after insert on x begin
   ...> insert into y select x from (with t as (select * from new) select x from t);
   ...> end;
sqlite>

(10) By anonymous on 2020-11-04 19:43:43 in reply to 1 [link] [source]

I think that use of CTEs (including recursive CTEs) in triggers can be useful (I have sometimes used it); like you, I have just found that it doesn't work if it isn't nested. I think this is an acceptable restriction for now.

(14) By anonymous on 2023-02-21 08:55:47 in reply to 10 [link] [source]

CTE and triggers are the bread and butter, and CTE should be supported within a trigger.

This is not supported, but I have been using it for years:

CREATE TRIGGER IF NOT EXISTS DCS_UPDATE_URI BEFORE UPDATE OF DCS_URI ON DEVICE_COM_SETTINGS --WHEN FCS(23) --WHEN ((SELECT F_FLAG FROM FLAGS WHERE F_NAME == 'SIGNAL_FLAG_03') == 2) BEGIN

-- SELECT RAISE (ABORT, 'Table DEVICE_COM_SETTINGS ((INSERT MASTER / STATND-ALONE): ERROR-96: The URI is malformed') -- WHERE (NEW.DDS_URI REGEXP '^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}');

DELETE FROM _INET_;
INSERT INTO _INET_
    WITH RECURSIVE SPLIT(IP_ADDRESS, LAST_OCTET, REMAINDER) AS (
        VALUES ('', '', NEW.DCS_URI)
        UNION ALL
        SELECT CASE
            WHEN LAST_OCTET == '.'
            THEN
                SUBSTR(REMAINDER, 1, 1)
            ELSE
                IP_ADDRESS || SUBSTR(REMAINDER, 1, 1)
        	END,
	SUBSTR(REMAINDER, 1, 1),
	SUBSTR(REMAINDER, 2)
  FROM SPLIT
  WHERE REMAINDER <> ''
)
SELECT
       REPLACE(IP_ADDRESS, '.','') AS 'OCTET'
FROM
       SPLIT
WHERE
       LAST_OCTET == '.' OR REMAINDER =='' ;

-- Debug

-- INSERT INTO Variables (NAME, VALUE) -- VALUES -- ('OCTET_1', (SELECT OCTET FROM _INET WHERE ROWID == 1)), -- ('OCTET_2', (SELECT OCTET FROM INET WHERE ROWID == 2)), -- ('OCTET_3', (SELECT OCTET FROM INET WHERE ROWID == 3)), -- ('OCTET_4', (SELECT OCTET FROM INET WHERE ROWID == 4));

SELECT CASE
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 1) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 1) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-07: The first octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 2) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 2) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-08: The second octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 3) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 3) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-09: The third octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 4) < 1) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 4) > 253)) AND ((SELECT SUM(OCTET) FROM _INET_) NOT IN (0, 1020))
        THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-10: The URI is invalid')
END;

Here is another example of a CTE in a trigger: -- Step Ⅻ : Check that all the devices in the daisy chain have status 4 WITH CHAIN (NODE, STATUS, DECOUPLED) AS ( SELECT NODE, STATUS, DECOUPLED FROM DCD_MODIFY_DAISY_CHAIN WHERE CHAIN_ID == (SELECT VALUE FROM _Variables WHERE NAME == 'CHAIN_ID' LIMIT 1) UNION SELECT NODE, STATUS, DECOUPLED FROM DCD_MODIFY_DAISY_CHAIN WHERE SERIAL_NUMBER == NEXT_PTR ) SELECT (CASE 1 WHEN (DECOUPLED != 1) AND (NODE >= (SELECT VALUE FROM _Variables WHERE NAME == 'NODE') - 1) THEN RAISE (ABORT, 'Table DCD_MODIFY_DAISY_CHAIN (DELETE): ERROR 80-03: The device isn''t decoupled') WHEN (STATUS != 4) THEN RAISE (ABORT, 'Table DCD_MODIFY_DAISY_CHAIN (DELETE): ERROR 80-04: Insertion of new node isn''t possible one or more devices have a status != 4') END) FROM CHAIN;

It's a shame that this one doesn't work: -- -- The following statement will cause an error -- WITH RECURSIVE NODES_TO_REMOVE (CHAIN_ID, NODE, SERIAL_NUMBER, NEXT_PTR) -- AS ( -- SELECT -- CHAIN_ID, -- NODE, -- SERIAL_NUMBER, -- NEXT_PTR -- FROM DCD_MODIFY_DAISY_CHAIN -- WHERE CHAIN_ID == (SELECT VALUE FROM _Variables WHERE NAME == 'CHAIN_ID') -- AND NODE > (SELECT VALUE FROM _Variables WHERE NAME == 'NODE') -- UNION -- SELECT -- CHAIN_ID, -- NODE, -- SERIAL_NUMBER, -- NEXT_PTR -- FROM DCD_MODIFY_DAISY_CHAIN -- WHERE SERIAL_NUMBER == NEXT_PTR -- ) -- DELETE FROM DAISY_CHAINED_DEVICES; -- WHERE (DCD_ID == (SELECT CHAIN_ID FROM NODES_TO_REMOVE)) -- AND (DCD_NODE IN (SELECT NODE FROM NODES_TO_REMOVE));

Give a yell if you want more examples of naughty CTE’s in triggers