1.Hozzuk létre a RAPID RALLY autóversenyre benevezett versenyzők tábláját. Készítsünk triggert, amely minden beillesztett versenyzőhöz megkísérel alábbi feltételeknek megfelelő partnert keresni: 1) a partnerek nemzetisége egyezzen meg, 2) a partnerek minősítése legyen 20 ponton belül, 3) a feladatkörök illeszkedjenek. Tötse fel a partner oszlopokat(!) a feltételeknek megfelelően.

DROP TABLE rally;
CREATE TABLE rally ( 
  név          VARCHAR2(25), 
  nemzetiség   VARCHAR2(20),
  feladatkör   VARCHAR2(9),
  minősítés    NUMBER(3),
  partner      VARCHAR2(25)
);

INSERT INTO rally VALUES ('Gyula'    ,'HUN'   ,'PILOT'    ,50,   '---');
INSERT INTO rally VALUES ('White'    ,'GBR'   ,'PILOT'    ,72,   '---');
INSERT INTO rally VALUES ('van Glut' ,'NED'   ,'PILOT'    ,60,   '---');
INSERT INTO rally VALUES ('Sándor'   ,'HUN'   ,'PILOT'    ,67,   '---');
INSERT INTO rally VALUES ('Piere'    ,'FRA'   ,'PILOT'    ,57,   '---');
INSERT INTO rally VALUES ('Miklós'   ,'HUN'   ,'PILOT'    ,80,   '---');
INSERT INTO rally VALUES ('Wraclaw'  ,'POL'   ,'NAVIGATOR',55,   '---');
INSERT INTO rally VALUES ('Nitzky'   ,'POL'   ,'NAVIGATOR',76,   '---');
INSERT INTO rally VALUES ('ONeil'    ,'GBR'   ,'NAVIGATOR',67,   '---');
INSERT INTO rally VALUES ('Kertész'  ,'HUN'   ,'NAVIGATOR',55,   '---');

set linesize 100
select * from rally;

CREATE OR REPLACE TRIGGER rally_insert
BEFORE INSERT ON rally
FOR EACH ROW
DECLARE
  CURSOR versenyzők IS SELECT * FROM rally WHERE partner = '---';
BEGIN
  FOR v IN versenyzők LOOP
    IF v.nemzetiség = :NEW.nemzetiség 
           AND ABS(v.minősítés - :NEW.minősítés) < 20 
           AND v.feladatkör != :NEW.feladatkör THEN
      :NEW.partner := v.név;
      UPDATE rally SET partner = :NEW.név WHERE név = v.név;
      EXIT;
    END IF;
  END LOOP;
END;
/

CREATE OR REPLACE TRIGGER rally_insert
BEFORE INSERT ON rally
FOR EACH ROW
DECLARE
  partnerneve VARCHAR2(20);
  CURSOR versenyzők IS SELECT név FROM rally WHERE partner = '---' 
    AND nemzetiség = :NEW.nemzetiség
    AND feladatkör != :NEW.feladatkör
    AND ABS(minősítés - :NEW.minősítés) < 20;
BEGIN
  OPEN versenyzők;
  FETCH versenyzők INTO partnerneve;
  IF versenyzők%FOUND THEN 
    :NEW.partner := partnerneve;
    UPDATE rally SET partner = :NEW.név WHERE név = partnerneve;
  END IF;
  CLOSE versenyzők;
END;
/


 
2.Hozzuk létre a ALVÓ LAJHÁR Sport Egyesület által szervezett bajnokság adatbázisát. Készítsünk triggert amelyik minden új résztvevő csapathoz legenerálja a lejátszandó mérkőzéseket a mérkőzések táblában. Minden csapat egy mérkőzést játszik az adott bajnokság többi csapatával.

DROP TABLE mérkőzések;
CREATE TABLE mérkőzések (
  bajnokságneve VARCHAR2(20),
  egyikcsapat VARCHAR2(20),
  másikcsapat VARCHAR2(20),
  eredmény VARCHAR2(20),
  időpont DATE
);
DROP TABLE résztvevők;
CREATE TABLE résztvevők (
  bajnokságneve VARCHAR2(20),
  csapatneve VARCHAR2(20)
);

INSERT INTO résztvevők VALUES ('Foci',         'Kék Pumák' );
INSERT INTO résztvevők VALUES ('Foci',         'Piros Malter az Égen' );
INSERT INTO résztvevők VALUES ('Kosárlabda',   'Butitime' );
INSERT INTO résztvevők VALUES ('Kosárlabda',   'Rózsaszín Kismalac' );
INSERT INTO résztvevők VALUES ('Foci',         'Hálószaggatók' );
INSERT INTO résztvevők VALUES ('Asztalitenisz','Ló van a kertembe' );
INSERT INTO résztvevők VALUES ('Foci',         'Hóhányók' );
INSERT INTO résztvevők VALUES ('Foci',         'Zöld Sasok' );
INSERT INTO résztvevők VALUES ('Kosárlabda',   'Sárga Irígység' );
INSERT INTO résztvevők VALUES ('Kosárlabda',   'Mérges Turul' );
INSERT INTO résztvevők VALUES ('Asztalitenisz','Zöld Taknyok' );
INSERT INTO résztvevők VALUES ('Asztalitenisz','Kétbalkéz SE' );

set linesize 100
select * from mérkőzések;

CREATE OR REPLACE TRIGGER mérkőzésgenerátor
BEFORE INSERT ON résztvevők
FOR EACH ROW
DECLARE
  CURSOR többicsapat IS SELECT * FROM résztvevők WHERE bajnokságneve = :NEW.bajnokságneve;
BEGIN
  FOR másik IN többicsapat LOOP
    INSERT INTO mérkőzések 
    VALUES (:NEW.bajnokságneve, :NEW.csapatneve, másik.csapatneve, NULL, NULL );
  END LOOP;
END;
/

CREATE OR REPLACE TRIGGER mérkőzésgenerátor
BEFORE INSERT ON résztvevők
FOR EACH ROW
BEGIN
    INSERT INTO mérkőzések 
    SELECT :NEW.bajnokságneve, :NEW.csapatneve, csapatneve, NULL, NULL 
    FROM résztvevők 
    WHERE bajnokságneve = :NEW.bajnokságneve;
END;
/

Írjunk másik triggert amelyik egy résztvevő törlése esetén törli annak a mérkőzéseit is.

CREATE OR REPLACE TRIGGER mérkőzéstörlés
BEFORE DELETE ON résztvevők
FOR EACH ROW
BEGIN
  DELETE FROM mérkőzések 
  WHERE bajnokságneve = :OLD.bajnokságneve 
  AND ( egyikcsapat = :OLD.csapatneve OR másikcsapat = :OLD.csapatneve );
END;
/