--CWICZENIE HORYZONTALNE ROZPRASZANIE DANYCH

CREATE TABLE employees_shipping (

    EMPLOYEE_ID	NUMBER(6,0) NOT NULL ENABLE,
FIRST_NAME	VARCHAR2(20 BYTE),
LAST_NAME	VARCHAR2(25 BYTE) NOT NULL ENABLE,
SALARY	NUMBER(8,2),
DEPARTMENT_ID	NUMBER(4,0),

CONSTRAINT employee_id_pk_shipping PRIMARY KEY (employee_id) ENABLE



);

drop table employees_shipping;
commit;



SELECT * FROM employees_others@foo;

CREATE SYNONYM employees_others FOR employees_others@foo;

SELECT * FROM employees_others;

create view employees as
select * from employees_shipping
union all
select * from employees_others;

CREATE OR REPLACE TRIGGER ON_EMPLOYEES_INSERT
INSTEAD OF INSERT ON employees
  DECLARE
  BEGIN
  CASE WHEN :NEW.department_id=50 THEN 
    INSERT INTO employees_shipping VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
  ELSE
    INSERT INTO employees_others VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
  END CASE;
  END;
  
  
  
  CREATE OR REPLACE TRIGGER ON_EMPLOYEES_DELETE
INSTEAD OF DELETE ON employees
  DECLARE
  BEGIN
  CASE WHEN :OLD.department_id=50 THEN 
    DELETE FROM employees_shipping em WHERE em.employee_id = :OLD.employee_id;
  ELSE
    DELETE FROM employees_others em WHERE em.employee_id = :OLD.employee_id;
  END CASE;
  END;
  
  CREATE OR REPLACE TRIGGER ON_EMPLOYEES_UPDATE
INSTEAD OF UPDATE ON employees
  DECLARE
  BEGIN
  CASE WHEN :OLD.department_id=:NEW.department_id THEN 
    CASE WHEN :OLD.department_id=50
      THEN 
        UPDATE employees_shipping em 
        SET em.employee_id = :NEW.employee_id, em.first_name = :NEW.first_name, em.last_name = :NEW.last_name, em.salary = :NEW.salary, em.department_id = :NEW.department_id;
      ELSE 
        UPDATE employees_others em
        SET em.employee_id = :NEW.employee_id, em.first_name = :NEW.first_name, em.last_name = :NEW.last_name, em.salary = :NEW.salary, em.department_id = :NEW.department_id;
      END CASE;
  ELSE
    CASE WHEN :OLD.department_id=50
    THEN 
     dbms_output.put_line('wartosci'||' '||:NEW.employee_id||' '|| :NEW.first_name||' '|| :NEW.last_name||' '|| :NEW.salary||' '|| :NEW.department_id);
    INSERT INTO employees_others VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
     DELETE FROM employees_shipping em WHERE em.employee_id = :OLD.employee_id;
     

    ELSE
      INSERT INTO employees_shipping VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
      DELETE FROM employees_others em WHERE em.employee_id = :OLD.employee_id;
      
    END CASE;
  END CASE;
  END;
  
  rollback;

INSERT INTO employees VALUES (3,'NIKODEM','DYZMA',300,50);
INSERT INTO employees VALUES (2,'UKSZ','NOW',300,51);

purge recyclebin;

SELECT * FROM employees_shipping;
SELECT * FROM employees_others;

commit;

--OTHERS
--2	UKSZ	NOW	300	51

--SHIPPING
--1	UKSZ	NOW	300	50
--3	NIKODEM	DYZMA	300	50


insert into employees_others values (3 ,'NIKODEM', 'DYZMA', 300, 33);
UPDATE employees em SET em.department_id = 33 WHERE em.employee_id = 3;



CREATE TABLE employees_others (

    EMPLOYEE_ID	NUMBER(6,0) NOT NULL ENABLE,
FIRST_NAME	VARCHAR2(20 BYTE),
LAST_NAME	VARCHAR2(25 BYTE) NOT NULL ENABLE,
SALARY	NUMBER(8,2),
DEPARTMENT_ID	NUMBER(4,0),

CONSTRAINT employee_id_pk_others PRIMARY KEY (employee_id) ENABLE



);