I was surprised that an SQL script didn't provide the result I expected. I translated my surprise into this question: how does a trigger initialize the "old" and "new" aliases?

Let's consider a table T defined as,

   create table T(Id int not null primary key,
                  X  int,
                  Y  int);

I want to increment column X whenever column Y is modified (think of X as an update counter). This is performed by trigger T_trigger.

   create trigger T_trigger after update of Y on T
   begin
     update T set X = X+1 where Id = old.Id;
   end;

Then I check this code (SQLite 3.40):

   insert into T values (1,1,1);
   update T set Y = 2;
   select * from T;

which works as expected:

   1|2|2

Now, by curiosity (actually, "by necessity", in the real application), I would like to examine how things work inside the trigger. I create a new table, TRACE, in which the trigger writes the states of the row being updated:

   create table T(Id int not null primary key,X int,Y int);
   create table TRACE(Id int,X int,Y int);
   create trigger T_trigger after update of Y on T
   begin
     insert into TRACE values (old.Id,old.X,old.Y);
     update T set X = X+1 where Id = old.Id;
     insert into TRACE values (old.Id,new.X,new.Y);
   end;

   insert into T values (1,1,1);
   update T set Y = 2;

   select * from TRACE;
   select '';
   select * from T;

Though the net result still is correct, I observe that the "new" state, as reported in table TRACE, doesn't reflect the incrementation update, despite the fact that it was captured after this internal update.

   1|1|1
   1|1|2  (!)

   1|2|2

It seems that the answer to my question is: both "old" and "new" versions are computed (and therefore frozen) before the execution of the trigger body.

Am I right? Thanks for any insight on this issue.

old and new are populated when the trigger is entered. new reflects changes made by the "main` query that caused the trigger to run in the first place, but not changes made by the trigger itself.

THe magic table old represents the old row values as they existed before the operation was performed. Logically old can exists only if there was an old row and do not exist for insert operations where there is no old row.

The magic table new represents the new row values as they exist after the peration is performed. Logically new can exists only if there was a new rosw and do not exist for delete operations wherethe is no new row.

Those two paragraphs should be quite sufficient to understand trigger tables. Note that everything is SQLite is done one after each or 'row after each' because that is how hooman technology works, so therefore the triggers are fires once for each row after each.

t seems that the answer to my question is: both "old" and "new" versions are computed (and therefore frozen) before the execution of the trigger body.

Obviously this must be the case. THere is absolutely no other way that it could work.

Indeed, this is how SQLite works but other SQL engines may have different execution models. For instance, ORACLE allows the body of a "before update/insert" trigger to overwrite the "new" values. So, my trigger could have been written:

   begin
      :new.X = :new.X + 1;
   end;

I haven't the opportunity to check this, but I guess that the following code could produce an interesting output:

   begin
      insert into TRACE values (:new.Id,:new.X,:new.Y);
      :new.X = :new.X + 1;
      insert into TRACE values (:new.Id,:new.X,:new.Y);
   end;

Maybe ORACLE experts (which I am not) could clarify this?

I would wonder why you are using the old.id to identify the row? THe row, after the operation is performed, has the new.id as the id. The row with the old.id no longer exists.

Just a careless copy of the source code. Since the application never updates this Id, the old and new values are always the same in the trigger body.

So it is a matter of hope and pray. Often used often wrongs modus operendi.

Yes indeed, the operation of triggers is as expected. All the triggers recieve the same "old" values (the row prior to the "operation" being performed on the row) and the same "new" values (the row with the "operation" being performed having been completed for the row).

create table x(x,y);
create table t(what,x,y);
create trigger xbi1 before insert on x begin
    insert into t values ('abi', new.x, new.y);
end;
create trigger xai1 after insert on x begin
    insert into t values ('xai1', new.x, new.y);
end;
create trigger xai2 after insert on x begin
    insert into t values ('xai2', new.x, new.y);
    update x set y = y+1 where rowid = new.rowid;
    insert into t values ('xai2', new.x, new.y);
end;
create trigger xai3 after insert on x begin
    insert into t values ('xai3', new.x, new.y);
end;
insert into x values(4,4);
select rowid,* from t;

┌───────┬────────┬───┬───┐
│ rowid │  what  │ x │ y │
├───────┼────────┼───┼───┤
│ 1     │ 'abi'  │ 4 │ 4 │
│ 2     │ 'xai3' │ 4 │ 4 │
│ 3     │ 'xai2' │ 4 │ 4 │
│ 4     │ 'xai2' │ 4 │ 4 │
│ 5     │ 'xai1' │ 4 │ 4 │
└───────┴────────┴───┴───┘