-- funkce pro kontrolu konsistence proveden� ��etn� operace

create or replace function ei_poradi_update() returns opaque as '
    declare zaznam_p RECORD;
    declare zaznam_m RECORD;
    declare zaznam_d RECORD;
    begin
    select into zaznam_p * from ei_popis where cislo=old.cislo;
    if not found then
        raise exception ''��etn� operace % nem� zadan� popis.'',old.cislo;
        end if;
    select into zaznam_m sum(castka) as castka 
           from ei_denik 
          where mdd=''M'' 
            and cislo=old.cislo;
    if zaznam_m.castka is null then
        raise exception ''��etn� operace % nem� stranu m� d�ti.'',old.cislo;
        end if;
    select into zaznam_d sum(castka) as castka 
           from ei_denik 
          where mdd=''D'' 
            and cislo=old.cislo;
    if zaznam_d.castka is null then
        raise exception ''��etn� operace % nem� stranu dal.'',old.cislo;
        end if;
    if zaznam_m.castka != zaznam_d.castka then
	raise exception ''��etn� operace % nem� shodnou stranu m� d�ti a dal.'',old.cislo;
        end if;
    return new;
    end;
' language 'plpgsql';

create trigger ei_poradi_update before update
    on ei_poradi for each row
    execute procedure ei_poradi_update();