Re: Capturing the changes in columns in a table

Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote...
>
>
> 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

Wow! Thanks Keith.  I will have to study these and understand them before getting into it.  Thanks again.

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