trunk/sql/09_backup_parts_trigger.sql

alter table backups alter column parts set default 0;

create or replace function backup_parts_check() returns trigger as '
declare
        b_parts integer;
        b_counted integer;
        b_id    integer;
begin
        -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
        if (TG_OP=''UPDATE'') then
                b_id := new.id;
                b_parts := new.parts;
        elsif (TG_OP = ''INSERT'') then
                b_id := new.id;
                b_parts := new.parts;
        end if;
        b_counted := (select count(*) from backup_parts where backup_id = b_id);
        -- raise notice ''backup % parts %'', b_id, b_parts;
        if ( b_parts != b_counted ) then
                raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
        end if;
        return null;
end;
' language plpgsql;

drop trigger do_backup_parts_check on backups;

create trigger do_backup_parts_check
        after insert or update or delete on backups
        for each row execute procedure backup_parts_check();

create or replace function backup_backup_parts_check() returns trigger as '
declare
        b_id            integer;
        old_b_id        integer;
        my_part_nr      integer;
        calc_part       integer;
begin
        if (TG_OP = ''INSERT'') then
                -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
                b_id = new.backup_id;
                my_part_nr = new.part_nr;
                execute ''update backups set parts = parts + 1 where id = '' || b_id;
        elsif (TG_OP = ''DELETE'') then
                -- raise notice ''trigger: % backup_id %, old.part_nr %'', TG_OP, old.backup_id, old.part_nr;
                b_id = old.backup_id;
                my_part_nr = old.part_nr - 1;
                execute ''update backups set parts = parts - 1 where id = '' || b_id;
        end if;
        calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
        if ( my_part_nr != calc_part ) then 
                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;
        end if;
        return null;
end;
' language plpgsql;

drop trigger do_backup_backup_parts_check on backup_parts;

create trigger do_backup_backup_parts_check
        after insert or update or delete on backup_parts
        for each row execute procedure backup_backup_parts_check();

1	dpavlin	324	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	dpavlin	329	-- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
10	dpavlin	324	if (TG_OP=''UPDATE'') then
11	dpavlin	329	b_id := new.id;
12			b_parts := new.parts;
13	dpavlin	324	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	dpavlin	329	create or replace function backup_backup_parts_check() returns trigger as '
33			declare
34			b_id integer;
35	iklaric	370	old_b_id integer;
36	dpavlin	329	my_part_nr integer;
37			calc_part integer;
38			begin
39			if (TG_OP = ''INSERT'') then
40	iklaric	371	-- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
41	dpavlin	329	b_id = new.backup_id;
42			my_part_nr = new.part_nr;
43			execute ''update backups set parts = parts + 1 where id = '' \|\| b_id;
44			elsif (TG_OP = ''DELETE'') then
45	iklaric	371	-- raise notice ''trigger: % backup_id %, old.part_nr %'', TG_OP, old.backup_id, old.part_nr;
46	dpavlin	329	b_id = old.backup_id;
47	iklaric	370	my_part_nr = old.part_nr - 1;
48	dpavlin	329	execute ''update backups set parts = parts - 1 where id = '' \|\| b_id;
49			end if;
50			calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
51	iklaric	370	if ( my_part_nr != calc_part ) then
52	iklaric	371	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;
53	dpavlin	329	end if;
54			return null;
55			end;
56			' language plpgsql;
57
58			drop trigger do_backup_backup_parts_check on backup_parts;
59
60			create trigger do_backup_backup_parts_check
61			after insert or update or delete on backup_parts
62			for each row execute procedure backup_backup_parts_check();
63