SQL>
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR);
Table created.
SQL> CREATE TABLE department
2 (dept_id INTEGER,
3 dept_name VARCHAR2(32));
Table created.
SQL>
SQL> CREATE OR REPLACE VIEW emp_public_data AS
2 SELECT e.emp_id,
3 e.emp_name,
4 d.dept_name
5 FROM employee e,
6 department d;
View created.
SQL> CREATE OR REPLACE TRIGGER emp_name_change
2 INSTEAD OF UPDATE ON emp_public_data
3 BEGIN
4 IF (:new.emp_name <> :old.emp_name)
5 OR (:old.emp_name IS NULL AND :new.emp_name IS NOT NULL) THEN
6 UPDATE employee
7 SET emp_name = :new.emp_name
8 WHERE emp_id = :new.emp_id;
9 END IF;
10 END;
11 /
Trigger created.
SQL>
SQL> drop table employee;
Table dropped.
SQL> drop table department;
Table dropped.
SQL>
SQL>