should allow using pseduo-cols(eg:new) inside a subquery within a trigger

[Pavan-Nambi]

turso> CREATE TABLE t (
    id INT PRIMARY KEY,
    n TEXT,
    m INT
);

CREATE TABLE t1 (
    eid INT PRIMARY KEY,
    d INT
);

CREATE TRIGGER trg AFTER INSERT ON t
BEGIN
    INSERT INTO t1(eid, d)
    SELECT NEW.id,
           COALESCE((SELECT d FROM t1 WHERE eid = NEW.m), -1) + 1;
END;

INSERT INTO t VALUES (1, 'CEO', NULL);
INSERT INTO t VALUES (2, 'VP', 1);
INSERT INTO t VALUES (3, 'Manager', 2);

SELECT * FROM t1 ORDER BY eid;

  x Parse error: no such table: new

turso>

sqlite:

sqlite> CREATE TABLE t (
    id INT PRIMARY KEY,
    n TEXT,
    m INT
);

CREATE TABLE t1 (
    eid INT PRIMARY KEY,
    d INT
);

CREATE TRIGGER trg AFTER INSERT ON t
BEGIN
    INSERT INTO t1(eid, d)
    SELECT NEW.id,
           COALESCE((SELECT d FROM t1 WHERE eid = NEW.m), -1) + 1;
END;

INSERT INTO t VALUES (1, 'CEO', NULL);
INSERT INTO t VALUES (2, 'VP', 1);
INSERT INTO t VALUES (3, 'Manager', 2);

SELECT * FROM t1 ORDER BY eid;
1|0
2|1
3|2
sqlite>