Re: Capturing the changes in columns in a table

On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera <[email protected]> wrote:

>That is an idea I have not thought about, Neal.  Thanks.  The boss comes
>up with lots of requests, and these have to be done yesterday. I will
>have to look into triggers. Have not used them yet. :-(

Here is some sample triggers that records the changes as they happen ...
create table t
(
    n integer primary key,
    a not null,
    b,
    c,
    d,
    e,
    idate not null,
    unique (a, idate)
);
create table audit_t
(
    a not null,
    changed not null,
    idate not null,
    variable not null,
    oldv,
    newv
);

create trigger t_upd_1 after insert on t begin
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'b',
           (select b from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.b as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'c',
           (select c from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.c as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'd',
           (select d from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.d as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'e',
           (select e from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.e as newv
     where newv <> oldv;
end;

create trigger t_upd_nocando before update of a, idate on t begin
  select raise(ABORT, 'You cannot update columns a or idate');
end;

create trigger t_upd_b after update of b on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'b',
         old.b,
         new.b;
end;
create trigger t_upd_c after update of c on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'c',
         old.c,
         new.c;
end;
create trigger t_upd_d after update of d on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'd',
         old.d,
         new.d;
end;
create trigger t_upd_e after update of e on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'e',
         old.e,
         new.e;
end;


insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8, '2019-02-13');

select * from audit_t;
a           changed           idate       variable    oldv        newv
----------  ----------------  ----------  ----------  ----------  ----------
p001        2458863.19728274  2019-02-12  b           1           10
p002        2458863.19728274  2019-02-12  c           2           4
p003        2458863.19728275  2019-02-12  d           n           y
p004        2458863.19728282  2019-02-13  b           4           5
p004        2458863.19728282  2019-02-13  c           2           3
p004        2458863.19728282  2019-02-13  d           y           n
p005        2458863.19728284  2019-02-13  c           2           3
p005        2458863.19728284  2019-02-13  e           4           8

update t set b=32 where a='p002' and idate=='2019-02-12';

select * from audit_t;
a           changed           idate       variable    oldv        newv
----------  ----------------  ----------  ----------  ----------  ----------
p001        2458863.19728274  2019-02-12  b           1           10
p002        2458863.19728274  2019-02-12  c           2           4
p003        2458863.19728275  2019-02-12  d           n           y
p004        2458863.19728282  2019-02-13  b           4           5
p004        2458863.19728282  2019-02-13  c           2           3
p004        2458863.19728282  2019-02-13  d           y           n
p005        2458863.19728284  2019-02-13  c           2           3
p005        2458863.19728284  2019-02-13  e           4           8
p002        2458863.19728295  2019-02-12  b           2           32

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users