1 alter table backups alter column parts set default 0;
3 create or replace function backup_parts_check() returns trigger as '
9 -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
10 if (TG_OP=''UPDATE'') then
13 elsif (TG_OP = ''INSERT'') then
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;
26 drop trigger do_backup_parts_check on backups;
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();
32 create or replace function backup_backup_parts_check() returns trigger as '
38 if (TG_OP = ''INSERT'') then
39 -- raise notice ''trigger: % backup_id %'', TG_OP, 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;
46 my_part_nr = old.part_nr;
47 execute ''update backups set parts = parts - 1 where id = '' || b_id;
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;
57 drop trigger do_backup_backup_parts_check on backup_parts;
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();