StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPL/SQL Assistance Needed with Trigger
text
Body
copied!<p>I am in the process of trying to create a trigger that when a record is inserted into the employee table(a new employee), the trigger fires and inserts a record into the employee_dept_history table as 'N/A' (since this employee is new and has no previous department). Also, if a current employee switches departments the trigger should insert a record in the employee_dept_history table and the employees depatment_id changes. I am having problems creating this trigger.I was wondering if someone could lead me to the right direction with my code. I can't get the below trigger to display 'N/A'. How could I go about making this trigger work? Do I need to create local variables for the new and old department?</p> <pre><code>CREATE OR REPLACE TRIGGER employee_dept_trigger BEFORE INSERT OR UPDATE ON employee FOR EACH ROW BEGIN INSERT INTO employee_dept_history VALUES(:NEW.employee_id, :NEW.employee_name, from_department_name (NEEDS TO OUTPUT 'N/A'), to_department_name, sysdate); END employee_dept_trigger; </code></pre> <p>The EMPLOYEE_DEPT_HISTORY table looks like:</p> <pre><code>CREATE TABLE empployee_dept_history ( EMPLOYEE_ID NUMBER(4) EMPLOYEE_NAME VARCHAR2(50) FROM_DEPARTMENT_NAME VARCHAR2(50) TO_DEPARTMENT_NAME VARCHAR2(50) OPERATION_TIME DATE ); </code></pre> <p>The EMPLOYEE table:</p> <pre><code>( EMPLOYEE_ID NUMBER(4) EMPLOYEE_NAME VARCHAR2(20) JOB VARCHAR2(50) MANAGER_ID NUMBER(4) HIRE_DATE DATE SALARY NUMBER(9) COMMISION NUMBER(9) DEPARTMENT_ID NUMBER(4) ); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload