Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Please Help me in understanding the following Question!

    Can anyone help me in understanding the following problem:

    I have to create a table to log employee terminations. Include the empno, emp name, the date hired, the date terminated, and the name of the oracle user who terminated the employee.

    Code:
    The EMP table is as follows:
    
    EMPNO , ENAME, JOB, MGR, HIREDATE, SAL,COMM,DEPTNO

    Code:
    Here is the DEPT table:
    
    DEPTNO, DNAME, LOC
    I just have HIREDATE with me and wondering how can I determine date of termination and the user who terminated?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why must it be a new table?
    why not just added new columns to the employee table?

    do you plan on writing a trigger?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    41
    As far as your questions are concerned:
    why must it be a new table?
    Ans: Because the problem statement says to "create a table to log employee terminations".
    Doesn't that mean that a new table is required?

    why not just added new columns to the employee table?
    Ans: Same as above.

    do you plan on writing a trigger?
    I do plan on writing a trigger but in the following question which specifically says that
    "Create a trigger on the emp table to insert relevant information into the employee termination log. The
    trigger must execute every time an employee is deleted."

    Please let me know if you have more questions.

    Thanks


    Quote Originally Posted by anacedent View Post
    why must it be a new table?
    why not just added new columns to the employee table?

    do you plan on writing a trigger?

  4. #4
    Join Date
    Apr 2013
    Posts
    41
    I believe, I just need to write the following code:

    Code:
    CREATE TABLE employee_terminations_log(
           empno   number(4),
    	   emp_name char(8),
    	   datehired date,
    	   dateterminated date,
    	   dbuser char(8) );

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    datatype CHAR should be avoided & always use VARCHAR2 to store string variables.
    USERNAME can be 30 characters long.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2013
    Posts
    41
    After creating the trigger as follows for the following question which I mentioned in my above post as well:

    Question: Create a trigger on the emp table to insert relevant information into the employee termination log. The trigger must execute every time an employee is deleted.


    Code:
    create or replace trigger emp_termination_trigger
    after delete of empno on emp
    for each row
    begin
          insert into employee_termination_log
    	  values(:old.empno,:old.ename,:old.hiredate,sysdate,user);
    end;

    I'm getting the following error:

    Code:
      8  /
    after delete of empno on emp
                 *
    ERROR at line 2:
    ORA-04073: column list not valid for this trigger type
    What's wrong with the line :
    Code:
    after delete of empno on emp
    Please let me know what went wrong in my above code?
    Thanks
    Last edited by Jack_Tauson_Sr; 05-07-13 at 22:23.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  CREATE OR replace TRIGGER emp_termination_trigger
      2    BEFORE DELETE ON EMP
      3    FOR EACH ROW
      4  BEGIN
      5      INSERT INTO employee_terminations_log
      6      VALUES     (:old.empno,
      7                  :old.ename,
      8                  :old.hiredate,
      9                  SYSDATE,
     10                  USER);
     11* END;
    SQL> /
    
    Trigger created.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2013
    Posts
    41
    Thanks a lot ! Appreciate your help and timely response.

    Quote Originally Posted by anacedent View Post
    Code:
      1  CREATE OR replace TRIGGER emp_termination_trigger
      2    BEFORE DELETE ON EMP
      3    FOR EACH ROW
      4  BEGIN
      5      INSERT INTO employee_terminations_log
      6      VALUES     (:old.empno,
      7                  :old.ename,
      8                  :old.hiredate,
      9                  SYSDATE,
     10                  USER);
     11* END;
    SQL> /
    
    Trigger created.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •