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.

the trigger doesn't sseem to be valid a UPdate of the same table should give an error — nbk, Jun 23 '23 at 14:48

score 0 · Answer 1 · answered Jun 23 '23 at 14:51

0

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;

answered Jun 23 '23 at 14:51

nbk

45,398
8
30
47

That seems like a good idea thanks, but what would append when multiple line is inserted at the same time ? Would NEW. change only the current row of the for each row statement ? – Adikte Jun 24 '23 at 11:54
I just made a few test but spatialite return an error while trying to execute the querry because of the NEW.Value : near ".": syntax error – Adikte Jun 24 '23 at 12:02
1

Triggers go row to row, so that will not be a problem. Please add the complete error message to your post, that abbreviated message from your comment doesn't help – nbk Jun 24 '23 at 12:04
`code`CREATE TRIGGER trig_after_update_arbres BEFORE INSERT ON arbres FOR EACH ROW BEGIN UPDATE arbres SET NEW.age = strftime('%Y', 'now') - strftime('%Y', NEW.dat_planta) + NEW.age_planta; END;`code` return near ".": syntax error `code`CREATE TRIGGER trig_after_update_arbres BEFORE INSERT ON arbres FOR EACH ROW BEGIN SET NEW.age = strftime('%Y', 'now') - strftime('%Y', NEW.dat_planta) + NEW.age_planta; END;`code` near "SET": syntax error , I don't get anything more precise working with spatialite and Qgis – Adikte Jun 24 '23 at 12:09
By the way i also tried to do the same thing with spatialite_gui from gaia and got exactly the same error mesage : " SQL error: near"SET": syntaxe error" using just SET NEW.age and "SQL error: near"SET": syntaxe error" while using UPDATE arbre set NEW.age – Adikte Jun 24 '23 at 12:34

How to work with a value modified in a spatialite trigger

1 Answers1