1 |
alter table backups alter column parts set default 0; |
2 |
|
3 |
create or replace function backup_parts_check() returns trigger as ' |
4 |
declare |
5 |
b_parts integer; |
6 |
b_counted integer; |
7 |
b_id integer; |
8 |
begin |
9 |
-- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id; |
10 |
if (TG_OP=''UPDATE'') then |
11 |
b_id := new.id; |
12 |
b_parts := new.parts; |
13 |
elsif (TG_OP = ''INSERT'') then |
14 |
b_id := new.id; |
15 |
b_parts := new.parts; |
16 |
end if; |
17 |
b_counted := (select count(*) from backup_parts where backup_id = b_id); |
18 |
-- raise notice ''backup % parts %'', b_id, b_parts; |
19 |
if ( b_parts != b_counted ) then |
20 |
raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted; |
21 |
end if; |
22 |
return null; |
23 |
end; |
24 |
' language plpgsql; |
25 |
|
26 |
drop trigger do_backup_parts_check on backups; |
27 |
|
28 |
create trigger do_backup_parts_check |
29 |
after insert or update or delete on backups |
30 |
for each row execute procedure backup_parts_check(); |
31 |
|
32 |
create or replace function backup_backup_parts_check() returns trigger as ' |
33 |
declare |
34 |
b_id integer; |
35 |
my_part_nr integer; |
36 |
calc_part integer; |
37 |
begin |
38 |
if (TG_OP = ''INSERT'') then |
39 |
-- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id; |
40 |
b_id = new.backup_id; |
41 |
my_part_nr = new.part_nr; |
42 |
execute ''update backups set parts = parts + 1 where id = '' || b_id; |
43 |
elsif (TG_OP = ''DELETE'') then |
44 |
-- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id; |
45 |
b_id = old.backup_id; |
46 |
my_part_nr = old.part_nr; |
47 |
execute ''update backups set parts = parts - 1 where id = '' || b_id; |
48 |
end if; |
49 |
calc_part := (select count(part_nr) from backup_parts where backup_id = b_id); |
50 |
if ( my_part_nr != calc_part ) then |
51 |
raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part; |
52 |
end if; |
53 |
return null; |
54 |
end; |
55 |
' language plpgsql; |
56 |
|
57 |
drop trigger do_backup_backup_parts_check on backup_parts; |
58 |
|
59 |
create trigger do_backup_backup_parts_check |
60 |
after insert or update or delete on backup_parts |
61 |
for each row execute procedure backup_backup_parts_check(); |
62 |
|