create or replace
procedure
  pridejPlast(  p_vyrobce     IN komponent.vyrobce%TYPE,
                p_model       IN komponent.model%TYPE,
                p_rok_vyroby  IN komponent.rok_vyroby%TYPE,
                p_polomer     IN plast.polomer%TYPE,
                p_sirka       IN plast.sirka%TYPE)
  AS
  BEGIN
    INSERT INTO komponent (vyrobce, model, rok_vyroby)
      VALUES (p_vyrobce, p_model, p_rok_vyroby);
    INSERT INTO plast (ko_vyrobce, ko_model, ko_rok_vyroby, polomer, sirka)
      VALUES (p_vyrobce, p_model, p_rok_vyroby, p_polomer, p_sirka);
  END pridejPlast;
/  
  
create or replace
trigger
  polomeryPlastu
  BEFORE INSERT OR UPDATE ON pouziva
  FOR EACH ROW
  DECLARE
    v_pasujeDoRamu NUMBER := 0;
    v_pasujeDoVidlice NUMBER := 0;
  BEGIN
    -- zjisti, jestli je mozne dany plast pouzit v nekterem z ramu, ktere jezdec pouziva (vzhledem k polomeru plaste)
    SELECT COUNT(*) INTO v_pasujeDoRamu
      FROM pouziva P JOIN ram R ON(role23_model=R.ko_model AND role23_vyrobce=R.ko_vyrobce AND role23_rok_vyroby=R.ko_rok_vyroby)
      WHERE P.role11_id_jezdce = :NEW.role11_id_jezdce AND
            R.polomer_kola=(SELECT plast.polomer FROM plast WHERE ko_vyrobce=:NEW.role23_vyrobce AND ko_model=:NEW.role23_model AND ko_rok_vyroby=:NEW.role23_rok_vyroby);
    
    -- zjisti, jestli je mozne dany plast pouzit v nektere z vidlic, ktere jezdec pouziva (vzhledem k polomeru plaste)
    SELECT COUNT(*) INTO v_pasujeDoVidlice
      FROM pouziva P JOIN vidlice R ON(role23_model=R.ko_model AND role23_vyrobce=R.ko_vyrobce AND role23_rok_vyroby=R.ko_rok_vyroby)
      WHERE P.role11_id_jezdce = :NEW.role11_id_jezdce AND
            R.polomer_kola=(SELECT plast.polomer FROM plast WHERE ko_vyrobce=:NEW.role23_vyrobce AND ko_model=:NEW.role23_model AND ko_rok_vyroby=:NEW.role23_rok_vyroby);
    
    IF (v_pasujeDoRamu=0 AND v_pasujeDoVidlice=0) THEN
      RAISE_APPLICATION_ERROR(-20208, 'Neni mozne pouzivat plast, ktery nepasuje ani do vidlice ani do ramu');
    END IF;
  END polomeryPlastu;
/