6. Create a complex view EMP_DEP_VIEW using an outer join between EMPLOYEE and DEPENDENT table with employee names, dependent’s birthdate and relation. The outer join will also return employees without any dependents. Now, create an INSTEAD OF trigger based on EMP_DEP_VIEW to enable you to delete employee 433 through view.

Question

What will be an ideal response?

Answer 1

```
SQL> CREATE VIEW emp_dep_view
2 AS
3 SELECT e.EmployeeId, Lname, Fname, depDOB, Relation
4 FROM employee e, dependent d
5 WHERE e.EmployeeId = d.EmployeeId (+);

View created.

SQL> CREATE OR REPLACE TRIGGER emp_delete_iod
2 INSTEAD OF DELETE ON emp_dep_view
3 FOR EACH ROW
4 BEGIN
5 DELETE FROM employee
6 WHERE EmployeeId = :OLD.EmployeeId;
7 END;
8 /

Trigger created.

SQL> DELETE FROM emp_dep_view
2 WHERE EmployeeId = 433;

1 row deleted.
```