1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
drop table if exists t1, t2, t3;
create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb;
create table t2 (op char(1), a int not null, b decimal (63,30));
create table t3 select 1 as i;
create trigger t1_bu before update on t1 for each row
begin
insert into t2 values ("u", old.a, old.b);
set new.b = old.b + 10;
end;//
create trigger t1_bd before delete on t1 for each row
begin
insert into t2 values ("d", old.a, old.b);
end;//
insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05);
update t1 set a=5 where a != 3;
select * from t1 order by id;
id a b
1 5 11.050000000000000000000000000000
2 5 12.050000000000000000000000000000
3 3 3.050000000000000000000000000000
4 5 14.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
u 1 1.050000000000000000000000000000
u 2 2.050000000000000000000000000000
u 4 4.050000000000000000000000000000
delete from t2;
update t1, t3 set a=6 where a = 5;
select * from t1 order by id;
id a b
1 6 21.050000000000000000000000000000
2 6 22.050000000000000000000000000000
3 3 3.050000000000000000000000000000
4 6 24.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
u 5 11.050000000000000000000000000000
u 5 12.050000000000000000000000000000
u 5 14.050000000000000000000000000000
delete from t2;
delete from t1 where a != 3;
select * from t1 order by id;
id a b
3 3 3.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
d 6 21.050000000000000000000000000000
d 6 22.050000000000000000000000000000
d 6 24.050000000000000000000000000000
delete from t2;
insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05);
delete t1 from t1, t3 where a != 3;
select * from t1 order by id;
id a b
3 3 3.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
d 1 1.050000000000000000000000000000
d 2 2.050000000000000000000000000000
d 4 4.050000000000000000000000000000
delete from t2;
insert into t1 values (4, 4, 4.05);
insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1;
select * from t1 order by id;
id a b
3 4 13.050000000000000000000000000000
4 5 14.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
u 3 3.050000000000000000000000000000
u 4 4.050000000000000000000000000000
delete from t2;
delete from t3;
insert into t3 values (4), (3);
insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1;
select * from t1 order by id;
id a b
3 5 23.050000000000000000000000000000
4 6 24.050000000000000000000000000000
select * from t2 order by op, a, b;
op a b
u 4 13.050000000000000000000000000000
u 5 14.050000000000000000000000000000
delete from t2;
replace into t1 (id, a) values (4, 1), (3, 1);
select * from t1 order by id;
id a b
3 1 0.000000000000000000000000000000
4 1 0.000000000000000000000000000000
select * from t2 order by op, a, b;
op a b
d 5 23.050000000000000000000000000000
d 6 24.050000000000000000000000000000
delete from t1;
delete from t2;
insert into t1 values (3, 1, 1.05), (4, 1, 2.05);
replace into t1 (id, a) (select i, 2 from t3);
select * from t1 order by id;
id a b
3 2 0.000000000000000000000000000000
4 2 0.000000000000000000000000000000
select * from t2 order by op, a, b;
op a b
d 1 1.050000000000000000000000000000
d 1 2.050000000000000000000000000000
delete from t1;
delete from t2;
insert into t1 values (3, 1, 1.05), (5, 2, 2.05);
load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a);
select * from t1 order by id;
id a b
3 4 0.000000000000000000000000000000
5 6 0.000000000000000000000000000000
select * from t2 order by op, a, b;
op a b
d 1 1.050000000000000000000000000000
d 2 2.050000000000000000000000000000
drop tables t1, t2, t3;
End of 5.0 tests
|
