CREATE TABLE ETUDIANT (
  ID INTEGER PRIMARY KEY,
  NOM TEXT NOT NULL,
  DdN DATE,
  PORTABLE TEXT
);

CREATE TABLE ETUDIANT_HISTORIQUE (
  ID INTEGER,
  NOM TEXT,
  DdN DATE,
  PORTABLE TEXT,
  TYPE_MODIF TEXT,
  DATE_MODIF TIMESTAMP
);

CREATE TRIGGER etudiant_historique_insert AFTER INSERT ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,PORTABLE,TYPE_MODIF,DATE_MODIF)
   VALUES (NEW.ID,NEW.NOM,NEW.DdN,NEW.PORTABLE,"INSERT",datetime('now'));
END;

CREATE TRIGGER etudiant_historique_update AFTER UPDATE ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,PORTABLE,TYPE_MODIF,DATE_MODIF)
   VALUES (NEW.ID,NEW.NOM,NEW.DdN,NEW.PORTABLE,"UPDATE",datetime('now'));
END;

CREATE TRIGGER etudiant_historique_delete AFTER DELETE ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,PORTABLE,TYPE_MODIF,DATE_MODIF)
   VALUES (OLD.ID,OLD.NOM,OLD.DdN,OLD.PORTABLE,"DELETE",datetime('now'));
END;

.mode column
.header ON
.width 2 13 10 14

INSERT INTO ETUDIANT (ID,NOM,DdN) 
       VALUES (1,"Jean Dubois","1989-08-21");
INSERT INTO ETUDIANT (ID,NOM,DdN,PORTABLE) 
       VALUES (3,"Pierre Martin","1987-05-12",NULL);
INSERT INTO ETUDIANT  
       VALUES (2,"Marie Dupont","1989-02-23","06.00.11.22.33");

UPDATE ETUDIANT SET PORTABLE = "06.10.20.30.40" WHERE ID = 1;

DELETE FROM ETUDIANT WHERE DdN < "1988-01-01";


SELECT * FROM ETUDIANT;

SELECT * FROM ETUDIANT_HISTORIQUE;