set serveroutput on

CREATE OR REPLACE VIEW emp_dept(empno, ename, sal, deptno, dname, loc) AS
SELECT empno, ename, sal, emp.deptno, dname, loc FROM emp, dept
WHERE emp.deptno = dept.deptno;

--teraz można zmiany po stronie klucza obcego czyli:
INSERT INTO emp_dept VALUES(8000,'LEON', 5000, 40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp_dept(deptno, dname, loc) VALUES(50, 'Skup butelek', 'Za monopolem');
INSERT INTO emp_dept(empno, ename, sal, deptno) VALUES(8000, 'LEON', 5000, 40);

------------------

--brakowało więzów
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;
INSERT INTO emp_dept VALUES(8000,'LEON', 5000, 40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp_dept(deptno, dname, loc) VALUES(50, 'Skup butelek', 'Za monopolem');
INSERT INTO emp_dept(empno, ename, sal, deptno) VALUES(8000, 'LEON', 5000, 40);
ROLLBACK;

------------------

CREATE OR REPLACE TRIGGER Wstawianie_emp_dept
INSTEAD OF INSERT
ON emp_dept
DECLARE
  pom1 INTEGER;
  pom2 INTEGER;
BEGIN
  DBMS_OUTPUT.Put_Line('Czesc jestem trigger Wstawianie_emp_dept. Dziekuje, ze mnie odpaliles:)');

  SELECT count(*) INTO pom1 FROM dept WHERE deptno = :NEW.deptno;

  IF :NEW.deptno IS NOT NULL AND pom1 = 0 THEN
    INSERT INTO dept(deptno, dname, loc) VALUES(:NEW.deptno, :NEW.dname, :NEW.loc);
  END IF;

  IF :NEW.empno IS NOT NULL OR
     :NEW.ename IS NOT NULL THEN
    INSERT INTO emp(empno, ename, sal, deptno) VALUES(:NEW.empno, :NEW.ename, :NEW.sal, :NEW.deptno);
  END IF;
END;
/

--nawet z nowym dzialem
INSERT INTO emp_dept VALUES(8000,'LEON', 5000, 50, 'Skup butelek', 'Za monopolem');
SELECT empno, ename, sal, deptno FROM emp WHERE empno = 8000;
SELECT * FROM dept WHERE deptno = 50;
ROLLBACK;

INSERT INTO emp_dept(deptno, dname, loc) VALUES(50, 'Skup butelek', 'Za monopolem');
SELECT * FROM dept WHERE dname LIKE 'Skup butelek';
ROLLBACK;

INSERT INTO emp_dept(empno, ename, sal, deptno) VALUES(8000, 'LEON', 5000, 40);
SELECT empno, ename, sal, deptno FROM emp WHERE empno = 8000;
ROLLBACK;

------------------

SELECT status FROM user_triggers WHERE trigger_name = 'WSTAWIANIE_EMP_DEPT';
SELECT status, object_type FROM user_objects WHERE object_name = 'WSTAWIANIE_EMP_DEPT';

ALTER TABLE emp DROP CONSTRAINT emp_dept_fk;
ALTER TABLE dept DROP CONSTRAINT dept_pk;

SELECT status FROM user_triggers WHERE trigger_name = 'WSTAWIANIE_EMP_DEPT';
SELECT status, object_type FROM user_objects WHERE object_name = 'WSTAWIANIE_EMP_DEPT';

------------------

--nawet z nowym dzialem
INSERT INTO emp_dept VALUES(8000,'LEON', 5000, 50, 'Skup butelek', 'Za monopolem');
SELECT empno, ename, sal, deptno FROM emp WHERE empno = 8000;
SELECT * FROM dept WHERE deptno = 50;
ROLLBACK;

SELECT status FROM user_triggers WHERE trigger_name = 'WSTAWIANIE_EMP_DEPT';
SELECT status, object_type FROM user_objects WHERE object_name = 'WSTAWIANIE_EMP_DEPT';

------------------

ALTER TRIGGER Wstawianie_emp_dept COMPILE;

SELECT status FROM user_triggers WHERE trigger_name = 'WSTAWIANIE_EMP_DEPT';
SELECT status, object_type FROM user_objects WHERE object_name = 'WSTAWIANIE_EMP_DEPT';

------------------

ALTER TRIGGER Wstawianie_emp_dept DISABLE;

SELECT status FROM user_triggers WHERE trigger_name = 'WSTAWIANIE_EMP_DEPT';
SELECT status, object_type FROM user_objects WHERE object_name = 'WSTAWIANIE_EMP_DEPT';

ALTER TRIGGER Wstawianie_emp_dept ENABLE;

------------------

--nawet bez więzów

INSERT INTO emp_dept VALUES(8000,'LEON', 5000, 50, 'Skup butelek', 'Za monopolem');
SELECT empno, ename, sal, deptno FROM emp WHERE empno = 8000;
SELECT * FROM dept WHERE deptno = 50;
ROLLBACK;

INSERT INTO emp_dept(deptno, dname, loc) VALUES(50, 'Skup butelek', 'Za monopolem');
SELECT * FROM dept WHERE dname LIKE 'Skup butelek';
ROLLBACK;

INSERT INTO emp_dept(empno, ename, sal, deptno) VALUES(8000, 'LEON', 5000, 40);
SELECT empno, ename, sal, deptno FROM emp WHERE empno = 8000;
ROLLBACK;

DROP TRIGGER wstawianie_emp_dept;