SQLite’s ‘NEW’ & ‘OLD’ Trigger Aliases and Keyword Classification

Issue Overview: ‘NEW’ and ‘OLD’ in Triggers vs. Keyword List Absence

The core issue revolves around the absence of ‘NEW’ and ‘OLD’ from SQLite’s official keyword list despite their prominent use in trigger definitions. Users familiar with SQL syntax often expect such identifiers to be reserved keywords, but SQLite treats them contextually. This discrepancy arises from SQLite’s unique approach to keyword classification, which prioritizes backward compatibility and minimal parser restrictions.

Key Observations

1. Trigger-Specific Aliases:
   In SQLite triggers, ‘NEW’ and ‘OLD’ are pseudo-aliases that reference the state of a row before (OLD) and after (NEW) a trigger event (e.g., UPDATE, INSERT, DELETE). They are not standalone keywords but contextual identifiers bound to trigger execution.
   Example Trigger:

   CREATE TRIGGER log_salary_change AFTER UPDATE OF salary ON employees  
   BEGIN  
     INSERT INTO audit_log (old_salary, new_salary)  
     VALUES (OLD.salary, NEW.salary);  
   END;  
   

   Here, OLD.salary and NEW.salary access column values from the row being updated.

2. Keyword Classification Rules:
   SQLite designates keywords based on whether they must be quoted when used as identifiers. Words like SELECT or WHERE are reserved because they cannot appear unquoted in schema or column names without causing parsing ambiguity. ‘NEW’ and ‘OLD’ do not meet this criterion:

   CREATE TABLE NEW(old INT);  -- Valid in SQLite  
   

   This table creation statement demonstrates that ‘NEW’ and ‘old’ are permissible as identifiers.

3. Documentation Structure:
   SQLite’s documentation explicitly limits discussions of ‘NEW’ and ‘OLD’ to the CREATE TRIGGER section. This compartmentalization reflects their role as trigger-specific constructs rather than general-purpose keywords.

Underlying Confusion
The confusion stems from conflating syntactic constructs with reserved keywords. While ‘NEW’ and ‘OLD’ are syntactically significant in triggers, they do not interfere with other SQL operations, allowing them to remain non-reserved. This design choice avoids breaking existing schemas that might use ‘NEW’ or ‘OLD’ as identifiers.

Possible Causes: Why ‘NEW’ & ‘OLD’ Are Not Keywords

1. Contextual Binding to Triggers
   SQLite’s parser resolves ‘NEW’ and ‘OLD’ exclusively within trigger bodies. Outside triggers, these identifiers have no special meaning. This contextual binding eliminates the need to reserve them globally. For example:

   SELECT NEW.value FROM some_table;  -- Error: 'NEW' undefined outside triggers  
   

   The parser treats ‘NEW’ as an ordinary identifier unless it appears in a trigger’s BEGIN...END block.

2. Minimal Keyword Reservation Policy
   SQLite adopts a conservative approach to keyword reservation to maximize backward compatibility. A word is reserved only if its unquoted use in an identifier would create parsing ambiguity. Since ‘NEW’ and ‘OLD’s roles are confined to triggers, reserving them would unnecessarily restrict their use in other contexts.

3. Parser Implementation Details
   SQLite’s parser is a hand-coded recursive descent parser that does not rely on a predefined keyword lookup table. Instead, it identifies keywords based on their position in the syntax tree. ‘NEW’ and ‘OLD’ are recognized only within trigger logic, where their usage is syntactically unambiguous.

4. Influence of Other Databases
   Users migrating from databases like PostgreSQL or Oracle might expect ‘NEW’ and ‘OLD’ to be reserved due to differences in keyword handling. For instance, PostgreSQL reserves ‘NEW’ and ‘OLD’ in trigger contexts but does not allow them as unquoted identifiers elsewhere. SQLite’s flexibility here is a deliberate divergence.

5. Documentation Gaps
   While SQLite’s documentation mentions ‘NEW’ and ‘OLD’ in trigger examples, it does not explicitly clarify their non-keyword status. This omission can lead to misconceptions, especially for developers accustomed to stricter keyword reservation in other SQL dialects.

Troubleshooting Steps, Solutions & Fixes

1. Verify Keyword Status

• Check Official Keyword List:
  Consult SQLite’s list of reserved keywords. ‘NEW’ and ‘OLD’s absence confirms they are not reserved.
• Test Identifier Validity:
  Execute DDL/DML statements using ‘NEW’ or ‘OLD’ as identifiers:

  CREATE TABLE OLD (NEW TEXT);  -- Succeeds if identifiers are unquoted  
  INSERT INTO OLD (NEW) VALUES ('test');  -- Valid  
  

  If these statements succeed, ‘NEW’ and ‘OLD’ are not keywords.

2. Resolve Trigger-Related Errors

• Scope of ‘NEW’/’OLD’:
  Ensure ‘NEW’ and ‘OLD’ are used only in triggers. Referencing them outside triggers will result in "no such column" errors.
• Qualify Column Names:
  In triggers, always prefix column names with NEW. or OLD.:

  CREATE TRIGGER update_employee AFTER UPDATE ON employees  
  BEGIN  
    INSERT INTO changes_log (old_name, new_name)  
    VALUES (OLD.name, NEW.name);  -- Correct  
  END;  
  

  Omitting the alias prefix (e.g., name instead of NEW.name) will cause the parser to look for a column in the trigger’s implicit FROM-clause table.

3. Handle Naming Conflicts

• Quoting Identifiers:
  If a schema uses ‘NEW’ or ‘OLD’ as identifiers, quote them with backticks or double-quotes to avoid conflicts in trigger logic:

  CREATE TABLE "OLD" (id INTEGER);  
  CREATE TRIGGER conflict_test AFTER INSERT ON "OLD"  
  BEGIN  
    INSERT INTO audit (action)  
    VALUES ('Inserted ID ' || NEW.id);  -- 'NEW' still works here  
  END;  
  

• Avoid Ambiguous Names:
  Rename columns/tables that use ‘NEW’ or ‘OLD’ if their dual role (identifier vs. trigger alias) complicates maintenance.

4. Comparative Analysis with Other Databases

• PostgreSQL:
  Reserves ‘NEW’ and ‘OLD’ as trigger-specific row variables. Using them as identifiers requires quoting:

  CREATE TABLE "NEW" (id SERIAL);  -- Quotes mandatory  
  

• Oracle:
  Similar to PostgreSQL, ‘NEW’ and ‘OLD’ are reserved in trigger contexts.
• MySQL/MariaDB:
  Allows ‘NEW’ and ‘OLD’ as unquoted identifiers outside triggers but reserves them within triggers.

5. Best Practices for Future-Proofing

• Quote All Identifiers:
  To avoid conflicts with future SQLite keywords, enclose identifiers in double-quotes or square brackets:

  CREATE TABLE "current" ("check" TEXT);  
  

• Monitor Keyword Updates:
  SQLite occasionally adds new keywords (e.g., WINDOW in v3.25.0). Review release notes for changes.
• Linter Tools:
  Use SQL validation tools (e.g., sqlfluff) to detect unquoted identifiers that match keywords.

6. Addressing Documentation Gaps

• Clarify Trigger Aliases:
  When documenting triggers, explicitly state that ‘NEW’ and ‘OLD’ are contextually bound aliases, not reserved keywords.
• Cross-Reference Empirical Lists:
  Reference third-party resources like Modern SQL’s Reserved Words List to compare SQLite’s behavior with other databases.

7. Advanced Parser Behavior

• Ambiguity-Driven Keyword Recognition:
  SQLite’s parser treats a word as a keyword only if it appears in a position where a keyword is syntactically valid. For example, SELECT is always a keyword, but NEW is only treated specially in triggers.
• Case Insensitivity:
  SQLite’s keyword recognition is case-insensitive for ASCII characters. nEw and NeW are equivalent to ‘NEW’ in triggers.

8. Debugging Common Scenarios

• Symptom: Syntax error near "NEW" in non-trigger SQL.
  Fix: Quote ‘NEW’ if using it as an identifier or remove it from non-trigger contexts.
• Symptom: no such column: NEW.column in a trigger.
  Fix: Ensure the column exists in the trigger’s target table and is prefixed with NEW. or OLD..

9. Historical Context

• SQL Standards Compliance:
  SQLite does not aim for full SQL standard compliance, opting instead for simplicity and flexibility. This explains its lenient keyword policy compared to standards like SQL:2011, which reserves ‘NEW’ for features like pipelined DML.
• Evolution of Triggers:
  SQLite added trigger support in v2.5.0 (2002). The ‘NEW’/’OLD’ syntax was adopted from PostgreSQL but without reserving the identifiers globally.

10. Community Resources

• SQLite Forum:
  Search for historical discussions about ‘NEW’ and ‘OLD’ to see how others have resolved edge cases.
• Schema Review Tools:
  Tools like sqlite-utils can automatically flag potential identifier/keyword conflicts.

By understanding SQLite’s keyword philosophy and the contextual role of ‘NEW’/’OLD’, developers can avoid common pitfalls while leveraging the database’s flexibility. Always prioritize quoting identifiers and consult empirical keyword lists when porting schemas across SQL dialects.