0

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.

  • Add the table definitions to your question. – Balazs Papp Aug 14 '16 at 16:51
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.