SELECT * FROM dolgozo;

/* 1/1 */
CREATE OR REPLACE TRIGGER dolgozo_trigger
BEFORE UPDATE ON dolgozo
FOR EACH ROW
BEGIN
    IF UPDATING ('FIZETES') THEN
        IF :OLD.foglalkozas = 'PRESIDENT' THEN
            :NEW.fizetes := :OLD.fizetes;
            DBMS_OUTPUT.PUT_LINE('A muvelet nem megengedett');
            INSERT INTO trigger_log (idopont, muvelet, esemeny) VALUES (SYSDATE, 'Fizetes emeles','Fizetes nem valtozott');

        ELSE
            IF :NEW.fizetes > 4000 THEN
                INSERT INTO trigger_log (idopont, muvelet, esemeny)
                VALUES (SYSDATE, 'Fizetes emeles',:OLD.fizetes||' ; '||:NEW.fizetes);
            END IF;
        END IF;
    END IF;
END;
/

UPDATE DOLGOZO SET FIZETES = 5000 WHERE DNEV = 'KING';
UPDATE DOLGOZO SET FIZETES = 5002 WHERE DNEV = 'SMITH';


SELECT * FROM DOLGOZO WHERE DNEV = 'KING';
SELECT* FROM DOLGOZO WHERE DNEV = 'SMITH';

DELETE FROM trigger_log;

/* 1/2 */
CREATE TABLE trigger_log2 (
  idopont DATE,
  muvelet VARCHAR2(20),
  uj_osszfiz NUMBER
);


CREATE OR REPLACE TRIGGER trigger2
AFTER INSERT OR DELETE OR UPDATE ON dolgozo
DECLARE
    event_type VARCHAR2(20);
    sum_sal NUMBER;
BEGIN
    IF INSERTING THEN
            event_type := 'INSERT';
        ELSIF DELETING THEN
            event_type := 'DELETE';
        ELSE
            event_type := 'UPDATE';
    END IF;

    SELECT SUM(fizetes) INTO sum_sal FROM DOLGOZO;

    IF sum_sal > 40000 THEN
          RAISE_APPLICATION_ERROR (-20000,'Tul nagy osszfizetes');
    END IF;

    INSERT INTO trigger_log2 (idopont, muvelet, uj_osszfiz) VALUES (SYSDATE, event_type,sum_sal);

END;
/

INSERT INTO dolgozo (DKOD, DNEV, FOGLALKOZAS, FONOKE, BELEPES, FIZETES, JUTALEK, OAZON) VALUES (1, 'DUMMY', 'MANAGER', 7902, NULL, 30000, 300, 20);
INSERT INTO dolgozo (DKOD, DNEV, FOGLALKOZAS, FONOKE, BELEPES, FIZETES, JUTALEK, OAZON) VALUES (3, 'DUMMY3', 'MANAGER', 7902, NULL, 100, 300, 20);
SELECT * FROM dolgozo;
SELECT * FROM trigger_log2;


/* 1/3 */

CREATE TABLE emp AS (SELECT * FROM NIKOVITS.emp);
CREATE TABLE dept AS (SELECT * FROM NIKOVITS.dept);

SELECT * FROM emp;

CREATE OR REPLACE VIEW dept_sal AS
   SELECT dname, SUM(sal) sumsal
   FROM dept, emp
   WHERE emp.deptno = dept.deptno
   GROUP BY dname;


SELECT * FROM dept_sal;
SELECT * FROM dept;
SELECT * FROM emp;


CREATE OR REPLACE TRIGGER trigger3
INSTEAD OF UPDATE ON dept_sal
DECLARE
    job_count NUMBER;
    job_id NUMBER;
BEGIN
    IF UPDATING('DNAME') THEN
        UPDATE dept SET dname = :NEW.dname WHERE dname = :OLD.dname;
    ELSIF UPDATING('SUMSAL') THEN
        SELECT deptno INTO job_id FROM dept WHERE dname = :NEW.dname;
        SELECT COUNT(*) INTO job_count FROM emp WHERE deptno = job_id;
        DBMS_OUTPUT.PUT_LINE(job_id || ' ' || job_count);
        UPDATE emp SET sal = sal + (:NEW.sumsal - :OLD.sumsal) / job_count WHERE deptno = job_id;
    ELSE
        RAISE_APPLICATION_ERROR (-20000,'Nem megengedett muvelet');
    END IF;
END;

UPDATE dept_sal SET dname = 'SALES' WHERE dname = 'BANKING';
UPDATE dept_sal SET sumsal = 40000 WHERE dname = 'SALES';

SELECT * FROM emp WHERE deptno = 30;

SELECT COUNT(*) FROM emp WHERE deptno = 30;

/* 1/4 */
CREATE TABLE ddl_log (
      tulajdonos VARCHAR2(30),
      obj_nev    VARCHAR2(30),
      obj_tipus  VARCHAR2(30),
      muvelet    VARCHAR2(400),
      idopont    DATE
);

CREATE OR REPLACE TRIGGER trigger4
AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_log (tulajdonos, obj_nev, obj_tipus, muvelet, idopont) VALUES (ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, ora_sysevent, SYSDATE);
END;


CREATE TABLE DUMMY(
    id NUMBER
);

SELECT * FROM DDL_LOG;

/* 1/5 */

CREATE TABLE naplo (
    datum   DATE,
    felh    VARCHAR2(100),
    gep     VARCHAR2(100),
    ip      VARCHAR2(100),
    osuser  VARCHAR2(100)
);

CREATE OR REPLACE TRIGGER trigger5
AFTER LOGON ON SCHEMA
BEGIN
    INSERT INTO naplo (datum, felh, gep, ip, osuser) VALUES (SYSDATE, SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYS_CONTEXT('USERENV','HOST'),
    SYS_CONTEXT('USERENV','IP_ADDRESS'),
    SYS_CONTEXT('USERENV','OS_USER'));
END;

SELECT * FROM naplo;