Xrhrft

I am trying to write a trigger that will insert into an empty table the employeeID, employeeName, old department, new department and effective date from two already existing tables employee and department. I have to retrieve that department name from the department table. I got a "bad bind variable" error from oracle and I don't know what I have to fix:

CREATE OR REPLACE TRIGGER emp_dept_change_trg

AFTER INSERT OR UPDATE ON EMPLOYEE

FOR EACH ROW



DECLARE

v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;



BEGIN



 SELECT DEPARTMENT_NAME

      INTO v_dept

      FROM DEPARTMENT

      WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;



IF INSERTING THEN

      INSERT INTO emp_dept_change

      VALUES(:NEW.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);



ELSIF UPDATING THEN



IF :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

INSERT INTO emp_dept_change

VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.DEPARTMENT_NAME, :NEW.DEPARTMENT_NAME ,SYSDATE);    





END IF;      

END IF;

END;

I am following logical steps as well:

Write a trigger emp_dept_change_trg that monitors the employee table as follows.

When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.

The OLD_DEPARTMENT_NAME is always '[New Hire]'.

Find the new department name from the department table based on the new department_id.

If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.

When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)

Find the old department name from the department table based on the old department_id.

If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be '[-----]'.

Find the new department name from the department table based on the new department_id.

If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.

The SYSDATE can be used in the EFFECTIVE_DATE column.

You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.

No temporary table/view/procedure/function is allowed in your trigger.

You can only use the department, employee, and emp_dept_change tables in your trigger.

You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
You have to select the values before. Something like this:

CREATE OR REPLACE TRIGGER emp_dept_change_trg

AFTER INSERT OR UPDATE ON EMPLOYEE

FOR EACH ROW



DECLARE

v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;



BEGIN



    SELECT DEPARTMENT_NAME

      INTO v_dept

      FROM DEPARTMENT

      WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;



  IF INSERTING THEN

      INSERT INTO emp_dept_change

      VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);



  ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN



    SELECT DEPARTMENT_NAME

      INTO v_old_dept

      FROM DEPARTMENT

      WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;



    INSERT INTO emp_dept_change

     VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);   

  END IF;

END;