SQLite Forum

RETURNING clause and TRIGGERS
Login

RETURNING clause and TRIGGERS

(1) By Mark Lawrence (mark) on 2021-02-05 08:36:56 [source]

The 5th limitation for the RETURNING clause says:

"The values emitted by the RETURNING clause are the values as seen by the top-level DELETE, INSERT, or UPDATE statement and do not reflect any subsequent value changes made by triggers. Thus, if the database includes AFTER triggers that modifies some of the values of each row inserted or updated, the RETURNING clause emits the original values that are computed before those triggers run."

The 4th limitation includes an example with a subquery to extract values from another table. My questions are:

  1. Given the naming restriction of the subquery (i.e. the need to reference the outside table explicitly) can the outside table be SELECTed from again[1] with an alias?

  2. If so, do the table values selected from in the subquery include modifications by AFTER triggers (even though the parameters in the query do not)?

  3. Perhaps more generally, do other tables in the subquery also reflect triggered changes?

[1]

    UPDATE t1
    SET
        b = b + 1
    RETURNING (
        SELECT alias.b
        FROM t1 alias
        WHERE alias.b = t1.a
      );