How to work with a value modified in a spatialite trigger

Question

I am have two tables one for the user to work with "arbres" and one is a record of what happened in the table "archive_arbres" insert. When a line is inserted in "arbres i want to calculate the value of the field age and the add the line with the calculated value in the table "archvive_arbres"

CREATE TABLE arbres(
  id INTEGER primary key autoincrement,
  age  INTEGER,
  dat_planta DATE NOT NULL,
  age_planta INT NOT NULL DEFAULT(0)
);
SELECT AddGeometryColumn('arbres', 'geometry', 2154, 'POINT', 'XY');


CREATE TABLE archive_arbres(
  id INTEGER primary key autoincrement,
  id_arbres TEXT,
  essence TEXT,
  age  INTEGER,
  dat_planta DATE ,
  age_planta INTEGER
);
SELECT AddGeometryColumn('archive_arbres', 'geometry', 2154, 'POINT', 'XY');


CREATE TRIGGER trig_after_update_arbres
AFTER INSERT ON arbres
FOR EACH ROW
BEGIN
    UPDATE arbres
    SET age = strftime('%Y', 'now') - strftime('%Y', NEW.dat_planta) + NEW.age_planta
    WHERE id = NEW.id;

    INSERT INTO archive_arbres (
        id_arbres, essence, age,
        dat_planta, age_planta, geometry)
    VALUES (
        NEW.id, NEW.essence, NEW.age,NEW.dat_planta, NEW.age_planta,
        NEW.geometry);
END;

The problem is that New.age contains the value by default which is 0,but i want the value set in the trigger how can i access it, spatialite doesn't let me use any variable.

Answer 1

in this case you change the NEw.age before it is insertet, that give you the right age.

CREATE TRIGGER trig_after_update_arbres
AFTER INSERT ON arbres
FOR EACH ROW
BEGIN
    
    SET NEW.age = strftime('%Y', 'now') - strftime('%Y', NEW.dat_planta) + NEW.age_planta;


    INSERT INTO archive_arbres (
        id_arbres, essence, age,
        dat_planta, age_planta, geometry)
    VALUES (
        NEW.id, NEW.essence, NEW.age,NEW.dat_planta, NEW.age_planta,
        NEW.geometry);
END;