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
