Trigger in Oracle SQL for preventing insertion of non-existent data

Question

The database has two tables - employee and department. When the user tries to insert a record into the employee table, a trigger is fired which checks the employee number entered by user if it is present in the department table. If the record is not found, it is not inserted into the employee table.

Primary and foreign keys must not be used. Only triggers are allowed.

Answer 1

Based on Scott's sample schema, I'm creating EMP_T and DEPT_T tables that contain only data; no constraints.

SQL> create table emp_t as select * From emp;

Table created.

SQL> create table dept_t as select * From dept;

Table created.

Trigger:

SQL> create or replace trigger trg_biu_emp_d
  2    before insert or update on emp_t
  3    for each row
  4  declare
  5    l_deptno dept_t.deptno%type;
  6  begin
  7    select d.deptno
  8      into l_deptno
  9      from dept_t d
 10      where d.deptno = :new.deptno;
 11  exception
 12    when no_data_found then
 13      raise_application_error(-20000, 'That department does not exist');
 14  end;
 15  /

Trigger created.

SQL>

Testing: failures first:

SQL> update emp_t set deptno = 50;
update emp_t set deptno = 50
       *
ERROR at line 1:
ORA-20000: That department does not exist
ORA-06512: at "SCOTT.TRG_BIU_EMP_D", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_EMP_D'


SQL> insert into emp_t (empno, ename, deptno) values (999, 'Littlefoot', 50);
insert into emp_t (empno, ename, deptno) values (999, 'Littlefoot', 50)
            *
ERROR at line 1:
ORA-20000: That department does not exist
ORA-06512: at "SCOTT.TRG_BIU_EMP_D", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_EMP_D'


SQL>

Testing #2: success:

SQL> update emp_t set deptno = 10;

14 rows updated.

SQL> insert into emp_t (empno, ename, deptno) values (999, 'Littlefoot', 20);

1 row created.

SQL>