mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
90 lines
3.5 KiB
Text
90 lines
3.5 KiB
Text
# DELETE
|
|
create table t (
|
|
s date, e date,
|
|
row_start SYS_TYPE as row start invisible,
|
|
row_end SYS_TYPE as row end invisible,
|
|
period for apptime(s, e),
|
|
period for system_time (row_start, row_end)) with system versioning;
|
|
insert into t values('1999-01-01', '2018-12-12'),
|
|
('1999-01-01', '1999-12-12');
|
|
select row_start into @ins_time from t limit 1;
|
|
select * from t order by s, e;
|
|
s e
|
|
1999-01-01 1999-12-12
|
|
1999-01-01 2018-12-12
|
|
delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
|
|
select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
|
|
from t for system_time all
|
|
order by s, e, row_start;
|
|
s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
|
|
1999-01-01 1999-12-12 OLD CURRENT ROW
|
|
1999-01-01 2000-01-01 NEW CURRENT ROW
|
|
1999-01-01 2018-12-12 OLD HISTORICAL ROW
|
|
2018-01-01 2018-12-12 NEW CURRENT ROW
|
|
# same for trigger case
|
|
delete from t;
|
|
delete history from t;
|
|
insert into t values('1999-01-01', '2018-12-12'),
|
|
('1999-01-01', '1999-12-12');
|
|
create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
|
|
create or replace procedure log(s text)
|
|
insert into log_tbl(log) values(s);
|
|
create trigger tr1upd_t before update on t
|
|
for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
|
|
create trigger tr2upd_t after update on t
|
|
for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
|
|
create trigger tr1del_t before delete on t
|
|
for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
|
|
create trigger tr2del_t after delete on t
|
|
for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
|
|
create trigger tr1ins_t before insert on t
|
|
for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
|
|
create trigger tr2ins_t after insert on t
|
|
for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
|
|
select row_start into @ins_time from t limit 1;
|
|
select * from t order by s, e;
|
|
s e
|
|
1999-01-01 1999-12-12
|
|
1999-01-01 2018-12-12
|
|
delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
|
|
select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
|
|
from t for system_time all
|
|
order by s, e, row_start;
|
|
s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
|
|
1999-01-01 1999-12-12 OLD CURRENT ROW
|
|
1999-01-01 2000-01-01 NEW CURRENT ROW
|
|
1999-01-01 2018-12-12 OLD HISTORICAL ROW
|
|
2018-01-01 2018-12-12 NEW CURRENT ROW
|
|
select * from log_tbl order by id;
|
|
id log
|
|
1 >DEL: 1999-01-01, 2018-12-12
|
|
2 >INS: 1999-01-01, 2000-01-01
|
|
3 <INS: 1999-01-01, 2000-01-01
|
|
4 >INS: 2018-01-01, 2018-12-12
|
|
5 <INS: 2018-01-01, 2018-12-12
|
|
6 <DEL: 1999-01-01, 2018-12-12
|
|
# UPDATE
|
|
create or replace table t (x int, s date, e date,
|
|
row_start SYS_TYPE as row start invisible,
|
|
row_end SYS_TYPE as row end invisible,
|
|
period for apptime(s, e),
|
|
period for system_time(row_start, row_end)) with system versioning;
|
|
insert into t values(1, '1999-01-01', '2018-12-12'),
|
|
(2, '1999-01-01', '1999-12-12');
|
|
select row_start into @ins_time from t limit 1;
|
|
select * from t;
|
|
x s e
|
|
1 1999-01-01 2018-12-12
|
|
2 1999-01-01 1999-12-12
|
|
update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
|
|
select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
|
|
from t for system_time all
|
|
order by x, s, e, row_start;
|
|
x s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
|
|
1 1999-01-01 2000-01-01 NEW CURRENT ROW
|
|
1 1999-01-01 2018-12-12 OLD HISTORICAL ROW
|
|
1 2018-01-01 2018-12-12 NEW CURRENT ROW
|
|
2 1999-01-01 1999-12-12 OLD CURRENT ROW
|
|
6 2000-01-01 2018-01-01 NEW CURRENT ROW
|
|
drop table t,log_tbl;
|
|
drop procedure log;
|