Search before asking

• I searched the issues and found no similar issues.

What Happened

From the SQLite docs (https://sqlite.org/lang_createtrigger.html) the references NEW and OLD can be used to refer to elements of the row that is subject to the trigger condition. However, when SQLFluff lints a SQLite trigger definition containing SELECT ... WHERE new.xyz or similar, it reports "Reference 'new.xyz' refers to table/view not found in the FROM clause or found in ancestor statement."

Note that this rule doesn't seem to be raised when the trigger contains a statement like DELETE FROM abc WHERE x = old.xyz, but that seems to be more because rule RF01 doesn't seem to apply to DELETE FROM statements.

Also note that SELECT ... WHERE new.xyz may seem odd within a trigger statement, but it is the canonical way to raise an exception conditionally within a SQLite trigger, as SELECT raise(ROLLBACK, 'not allowed') WHERE new.xyz = 'not-permitted'.

Expected Behaviour

Linting successful with no errors found.

Observed Behaviour

== [stdin] FAIL                                                                                                                                                
L:   1 | P:  57 | RF01 | Reference 'new.xyz' refers to table/view not found in
                       | the FROM clause or found in ancestor statement.
                       | [references.from]
All Finished 📜 🎉!

How to reproduce

echo 'CREATE TRIGGER x AFTER INSERT ON y BEGIN SELECT 1 WHERE new.xyz = 3 END;' | sqlfluff lint --dialect=sqlite -

Dialect

sqlite

Version

sqlfluff, version 3.2.5
Python 3.11.10

Configuration

N/A

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct