DATABÁZA              SDH

 

CREATE PROCEDURE kontroluj_kod_dod(kod INTEGER)

        DEFINE pocet INT;

 

        LET pocet = ( SELECT COUNT(*)

                      FROM adres

                      WHERE kod_dod=kod);

      IF pocet = 0 THEN

        RAISE EXCEPTION -746, 0, 'Dodavatel s tymto kodom neexistuje';

        END IF

            END PROCEDURE;

 

CREATE PROCEDURE kontroluj_priv_user()

DEFINE priv CHAR;

LET priv = ( SELECT usertype FROM sysusers

                  WHERE username = USER);

IF ((SELECT COUNT(username) FROM sysusers WHERE username = USER)=0) THEN

LET priv='C';

END IF

IF priv != 'D' THEN

RAISE EXCEPTION -746, 0, 'Na túto operáciu nemáte oprávnenie!';

END IF

END PROCEDURE;

 

create procedure kontroluj_cenu(stara INT, nova INT)

            if nova > 1.5*stara then

            raise exception -746, 0, 'velke DPH';

            end if

end procedure;

 

 

{ TABLE adres row size = 55 number of columns = 5 index size = 12 }

create table adres

  (

    kod_dod serial not null ,

    nazov char(20),

    psc char(6),

    mesto char(15),

    tel char(10),

    primary key (kod_dod) constraint pk_kod_dod

  );

 

 

{ TABLE soft row size = 49 number of columns = 6 index size = 24 }

create table soft

  (

    por_cislo serial not null ,

    nazov char(30),

    verzia char(3),

    cena integer,

    datum date,

    kod_dod integer not null ,

    primary key (por_cislo)  constraint por_cislo_pk

  );

 

 

 

alter table soft add constraint (foreign key (kod_dod)

    references adres (kod_dod) on delete cascade);

 

 

create trigger exist_kod insert on soft

referencing new as new_val

    for each row

        (

        execute procedure kontroluj_kod_dod(new_val.kod_dod

    ));

 

set triggers exist_kod disabled;

 

create trigger sel_trig_bef select of datum on soft

    before

        (

        execute procedure kontroluj_priv_user());

 

set triggers sel_trig_bef  disabled ;

 

create trigger upd_cena update of cena on soft

    referencing old as stara new as nova

    for each row

        (

        execute procedure kontroluj_cenu(stara.cena ,

    nova.cena ));