1. --CWICZENIE HORYZONTALNE ROZPRASZANIE DANYCH
2.  
3. CREATE TABLE employees_shipping (
4.  
5.     EMPLOYEE_ID NUMBER(6,0) NOT NULL ENABLE,
6. FIRST_NAME      VARCHAR2(20 BYTE),
7. LAST_NAME       VARCHAR2(25 BYTE) NOT NULL ENABLE,
8. SALARY  NUMBER(8,2),
9. DEPARTMENT_ID   NUMBER(4,0),
10.  
11. CONSTRAINT employee_id_pk_shipping PRIMARY KEY (employee_id) ENABLE
12.  
13.  
14.  
15. );
16.  
17. drop table employees_shipping;
18. commit;
19.  
20.  
21.  
22. SELECT * FROM employees_others@foo;
23.  
24. CREATE SYNONYM employees_others FOR employees_others@foo;
25.  
26. SELECT * FROM employees_others;
27.  
28. create view employees as
29. select * from employees_shipping
30. union all
31. select * from employees_others;
32.  
33. CREATE OR REPLACE TRIGGER ON_EMPLOYEES_INSERT
34. INSTEAD OF INSERT ON employees
35.   DECLARE
36.   BEGIN
37.   CASE WHEN :NEW.department_id=50 THEN
38.     INSERT INTO employees_shipping VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
39.   ELSE
40.     INSERT INTO employees_others VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
41.   END CASE;
42.   END;
43.  
44.  
45.  
46.   CREATE OR REPLACE TRIGGER ON_EMPLOYEES_DELETE
47. INSTEAD OF DELETE ON employees
48.   DECLARE
49.   BEGIN
50.   CASE WHEN :OLD.department_id=50 THEN
51.     DELETE FROM employees_shipping em WHERE em.employee_id = :OLD.employee_id;
52.   ELSE
53.     DELETE FROM employees_others em WHERE em.employee_id = :OLD.employee_id;
54.   END CASE;
55.   END;
56.  
57.   CREATE OR REPLACE TRIGGER ON_EMPLOYEES_UPDATE
58. INSTEAD OF UPDATE ON employees
59.   DECLARE
60.   BEGIN
61.   CASE WHEN :OLD.department_id=:NEW.department_id THEN
62.     CASE WHEN :OLD.department_id=50
63.       THEN
64.         UPDATE employees_shipping em
65.         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;
66.       ELSE
67.         UPDATE employees_others em
68.         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;
69.       END CASE;
70.   ELSE
71.     CASE WHEN :OLD.department_id=50
72.     THEN
73.      dbms_output.put_line('wartosci'||' '||:NEW.employee_id||' '|| :NEW.first_name||' '|| :NEW.last_name||' '|| :NEW.salary||' '|| :NEW.department_id);
74.     INSERT INTO employees_others VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
75.      DELETE FROM employees_shipping em WHERE em.employee_id = :OLD.employee_id;
76.      
77.  
78.     ELSE
79.       INSERT INTO employees_shipping VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.salary, :NEW.department_id);
80.       DELETE FROM employees_others em WHERE em.employee_id = :OLD.employee_id;
81.      
82.     END CASE;
83.   END CASE;
84.   END;
85.  
86.   rollback;
87.  
88. INSERT INTO employees VALUES (3,'NIKODEM','DYZMA',300,50);
89. INSERT INTO employees VALUES (2,'UKSZ','NOW',300,51);
90.  
91. purge recyclebin;
92.  
93. SELECT * FROM employees_shipping;
94. SELECT * FROM employees_others;
95.  
96. commit;
97.  
98. --OTHERS
99. --2     UKSZ    NOW     300     51
100.  
101. --SHIPPING
102. --1     UKSZ    NOW     300     50
103. --3     NIKODEM DYZMA   300     50
104.  
105.  
106. insert into employees_others values (3 ,'NIKODEM', 'DYZMA', 300, 33);
107. UPDATE employees em SET em.department_id = 33 WHERE em.employee_id = 3;
108.  
109.  
110.  
111. CREATE TABLE employees_others (
112.  
113.     EMPLOYEE_ID NUMBER(6,0) NOT NULL ENABLE,
114. FIRST_NAME      VARCHAR2(20 BYTE),
115. LAST_NAME       VARCHAR2(25 BYTE) NOT NULL ENABLE,
116. SALARY  NUMBER(8,2),
117. DEPARTMENT_ID   NUMBER(4,0),
118.  
119. CONSTRAINT employee_id_pk_others PRIMARY KEY (employee_id) ENABLE
120.  
121.  
122.  
123. );
124.  
125.