create or replace
PROCEDURE
  pridejCelkovaCastka
  AS
    v_dummy INTEGER;
  BEGIN
    SELECT 1 INTO v_dummy
      FROM USER_TAB_COLUMNS
      WHERE table_name = 'jezdec' AND column_name = 'celkova_castka';
      
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXECUTE IMMEDIATE 'ALTER TABLE jezdec ADD celkova_castka NUMBER DEFAULT 0';
  
      -- spocti utracene castky pro vsechny jezdce
      FOR radek IN (SELECT id_jezdce FROM jezdec)
      LOOP
        -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
        EXECUTE IMMEDIATE
          'UPDATE jezdec
            SET celkova_castka = (SELECT COALESCE(SUM(cena),0) FROM nakup WHERE je_id_jezdce = :1)
            WHERE id_jezdce = :2'
          USING radek.id_jezdce, radek.id_jezdce; 
      END LOOP;
    
  END pridejCelkovaCastka;
/

create or replace
TRIGGER
  upravCastkuZaKomponenty
  AFTER INSERT OR UPDATE OR DELETE ON nakup
  FOR EACH ROW
  DECLARE
    v_castka NUMBER := 0;
    v_id jezdec.id_jezdce%TYPE;
  BEGIN 
    IF DELETING THEN
      v_id := :OLD.je_id_jezdce;
    ELSE
      v_id := :NEW.je_id_jezdce;
    END IF;
    
    -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
    EXECUTE IMMEDIATE
      'SELECT celkova_castka
        FROM jezdec
        WHERE id_jezdce = :1'
      INTO v_castka
      USING v_id;
      
      
    IF INSERTING THEN
      v_castka := v_castka + :NEW.cena;
    ELSE IF DELETING THEN
      v_castka := v_castka - :OLD.cena;
    ELSE -- IF UPDATING
      v_castka := v_castka - :OLD.cena + :NEW.cena;
    END IF;
    END IF;

    -- je nutne pouzivat execute immediate, protoze v dobe kompilace sloupec jeste neexistuje
    EXECUTE IMMEDIATE
      'UPDATE jezdec
          SET celkova_castka = :1
          WHERE id_jezdce = :2'
      USING v_castka, v_id;
        
  END upravCastkuZaKomponenty;
/

/*
-- NEFUNKCNI TRIGGER - nepouzivat
-- dochazi ke konfliktu pristupu k blokovanemu prostredku
create or replace
TRIGGER
  vytvorSloupecCastka
  BEFORE INSERT OR UPDATE OR DELETE ON nakup
  DECLARE
    -- je nutne to spoustet jako samostatnou transakci, protoze se pouziva DDL
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN 
    -- pokud sloupec celkova_castka jeste neexistuje, vytvor ho a napln ho spravnymi hodnotami
    pridejCelkovaCastka();
  END vytvorSloupecCastka;
/
*/