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 *
Error report:
SQL Error: ORA-02055: distributed update operation failed; rollback required
ORA-00001: unique constraint (STUDENT.EMPLOYEE_ID_PK_OTHERS) violated
ORA-02063: preceding line from
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
);
ORA-06512: at "STUDENT.ON_EMPLOYEES_UPDATE", line 9
ORA-04088: error during execution of trigger 'STUDENT.ON_EMPLOYEES_UPDATE'
02055. 00000 - "distributed update operation failed; rollback required"
*Cause: a failure during distributed update operation may not have
rolled back all effects of the operation. Since
some sites may be inconsistent, the transaction must roll back to
savepoint or entirely
*Action: rollback to a savepoint or rollback transaction and resubmit
rollback complete.