Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote...
>
>
> On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera
> <jic...@outlook.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
