Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Join Date
    Dec 2009
    Posts
    20

    Unanswered: I need some serious help with delete and update triggers

    Ok...I'm at a loss. I need to write some triggers in SQL Developer. The first trigger is so whenever a record in the Job History table is deleted, the trigger will automatically archive the record into JobHistoryArchive.


    The second trigger is so whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. The linking fields between the two tables is the Employee_ID.

    In my example, I'm just using the sample database from Oracle Express Edition. I've never been so frazzled. I am at a loss for how I am going to achieve these triggers. Anyone that can help would be much appreciated. Here's what I've got so far...

    CREATE TABLE JobHistoryArchive
    (EmployeeID Int,
    StartDate Date,
    EndDate Date,
    JobID Int);

    CREATE TRIGGER JobHistoryDelete
    AFTER DELETE ON HR.job_history
    REFERENCING Old AS OldRow
    FOR EACH ROW
    DECLARE
    PRAGMA Autonomous_Transaction;
    BEGIN
    INSERT INTO JobHistoryArchive (EmployeeID, StartDate,
    EndDate, JobID)
    VALUES (Old.Employee_ID, Old.Start_Date,
    Old.End_Date, Old.Job_ID);
    COMMIT;
    END;


    CREATE TRIGGER UpdateJobHistory
    AFTER UPDATE ON HR_Employees
    FOR EACH ROW
    DECLARE
    PRAGMA Autonomous_Transaction;
    BEGIN
    Delete HR.Job_History (Employee_ID, Start_Date,
    End_Date, Job_ID Where HR_Employees.Employee_ID = HR.JobHistory.Employee_ID;
    COMMIT;
    END;

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by cianadalton View Post
    In my example, I'm just using the sample database from Oracle Express Edition. I've never been so frazzled. I am at a loss for how I am going to achieve these triggers.
    What do you mean with "achieve"? Triggers are fired, when corresponding action (here DELETE FROM job_history and UPDATE HR_Employees) is performed.

    Just three observations, as both posted triggers are not valid:
    - in the first trigger, you state "REFERENCING Old AS OldRow", anyway you reference old row values as Old. It should be :OldRow (semicolon is required); or you may get rid of that REFERENCING clause at all and use simple :old (again, note the starting semicolon). In the second trigger, you should reference it the same way (:new or :old depending on whether you want previous or actual EMPLOYEE_ID value; if EMPLOYEE_ID is immutable, it does not matter; I would use :new, but it is just matter of taste)
    - DELETE statement syntax in the second trigger is invalid - at least one left parenthesis and some column names after it are extra
    - as you use PRAGMA AUTONOMOUS_TRANSACTION, trigger changes will persist even when the firing statement will be rolled back. Probably not the behaviour you would want. And yes, remove COMMIT from the trigger as well, it is the work of the caller.

    When writing SQL and PL/SQL code, please consult SQL Reference and PL/SQL User's Guide Reference books for correct syntax. Both available on http://tahiti.oracle.com/.
    For running them in SQL Developer, please consult its help. It is accessible by F1 key. I have no idea what exactly you did with this code.
    If you would use sqlplus, all your input and Oracle response would be available for further investigation (yes, I recommend you to use it).

    [Edit: Just some formatting]

  3. #3
    Join Date
    Dec 2009
    Posts
    20
    What I mean by achieve is coding them correctly...I mean writing them not firing them.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What I mean by achieve is coding them correctly.
    proper syntax is clearly provided in previously mentioned manuals.

    Alternatively you could actually SEARCH this forum for previously posted triggers
    or GOOGLE is your friend when you actually use it.

    Have A Nice Day (HAND)!
    Last edited by anacedent; 12-06-09 at 11:56. Reason: fixed minor typo
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    20
    Ok....so thanks to both of your help, I was able to figure out my problems with the first trigger. The second trigger is still giving me grief. I edited it a bit but it's still fighting me. Anyone got a clue what's wrong. Once again, here's what I'm trying to do. Whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted.


    CREATE TABLE EmployeeArchive
    (EmployeeID Int, FirstName, LastName,
    EMail, PhoneNumber Int, HireDate Date, JobID, Salary Int,
    Commission Int, ManagerID, DepartmentID);

    CREATE TRIGGER UpdateJobHistory
    AFTER DELETE ON HR_Employees
    FOR EACH ROW
    DECLARE
    PRAGMA Autonomous_Transaction;
    BEGIN
    INSERT INTO EmployeeArchive
    (EmployeeID, FirstName, LastName,
    EMail, PhoneNumber, HireDate, JobID, Salary,
    Commission, ManagerID, DepartmentID)
    VALUES (:New.EmployeeID, :New.FirstName, :New.LastName,
    :New.EMail, :New.PhoneNumber, :New.HireDate,
    :New.JobID, :New.Salary, :New.Commission,
    :New.ManagerID, :New.DepartmentID);
    BEGIN
    DELETE HR.JobHistory(EmployeeID, StartDate,
    EndDate, JobID)
    WHERE Hr.JobHistory.EmployeeID = EmployeeArchive.New.EmployeeID
    END;

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You must have the same number of END statements as you have BEGIN statements.


    >The second trigger is still giving me grief.
    Not a valid Oracle error code & message

    using sqlplus along with CUT & PASTE show us your trigger & how Oracle responds as demonstrated below

    Code:
      1  CREATE TRIGGER UpdateJobHistory
      2  AFTER DELETE ON HR_Employees
      3  FOR EACH ROW
      4  DECLARE
      5  PRAGMA Autonomous_Transaction;
      6  BEGIN
      7  INSERT INTO EmployeeArchive
      8  (EmployeeID, FirstName, LastName,
      9  EMail, PhoneNumber, HireDate, JobID, Salary,
     10  Commission, ManagerID, DepartmentID)
     11  VALUES (:New.EmployeeID, :New.FirstName, :New.LastName,
     12  :New.EMail, :New.PhoneNumber, :New.HireDate,
     13  :New.JobID, :New.Salary, :New.Commission,
     14  :New.ManagerID, :New.DepartmentID);
     15  BEGIN
     16  DELETE HR.JobHistory(EmployeeID, StartDate,
     17  EndDate, JobID)
     18  WHERE Hr.JobHistory.EmployeeID = EmployeeArchive.New.EmployeeID
     19* END;
    SQL> /
    AFTER DELETE ON HR_Employees
                    *
    ERROR at line 2:
    ORA-00942: table or view does not exist
    http://download.oracle.com/docs/cd/B...5.htm#i2117787
    Last edited by anacedent; 12-07-09 at 00:14.
    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.

  7. #7
    Join Date
    Dec 2009
    Posts
    20
    Ok...it now compiles but with warnings.

    CREATE TABLE EmployeeArchive
    (EmployeeID Int, FirstName Char, LastName Char,
    EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
    Commission Int, ManagerID Int, DepartmentID Char);

    CREATE TRIGGER UpdateJobHistory
    AFTER DELETE ON HR.Employees
    FOR EACH ROW
    DECLARE
    PRAGMA Autonomous_Transaction;
    BEGIN
    INSERT INTO EmployeeArchive
    (EmployeeID, FirstName, LastName,
    EMail, PhoneNumber, HireDate, JobID, Salary,
    Commission, ManagerID, DepartmentID)
    VALUES (:Old.Employee_ID, :Old.First_Name, :Old.Last_Name,
    :Old.EMail, :Old.Phone_Number, :Old.Hire_Date,
    :Old.Job_ID, :Old.Salary, :Old.Commission_Pct,
    :Old.Manager_ID, :Old.Department_ID);
    End;
    BEGIN
    DELETE HR.Job_History(Employee_ID, Start_Date,
    End_Date, Job_ID)
    WHERE Hr.Job_History.Employee_ID = EmployeeArchive.New.EmployeeID
    END;

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    using sqlplus along with CUT & PASTE show us your trigger & how Oracle responds as demonstrated below

    DELETE
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    20
    Ok...so, I ran the trigger in SQL Plus and all it says is, "Warning: Trigger created with complication errors". Is there a way to get a more specific description as to what the errors are? I'm really not familiar with commands in SQL Plus. I've been using SQL Developer. Thanks, again.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    compile the trigger inside/with sqlplus

    When you see below then do
    >Warning: Trigger created with complication errors
    SQL> SHOW ERROR

    CUT & PASTE whole session back here so I see EXACTLY what you see.
    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.

  11. #11
    Join Date
    Dec 2009
    Posts
    20
    Ok...figured out how to get more specific answers. It's telling me that at 12/1 (:OldRow.Email, ) "PL/SQL: SQL Statement ignored" and at 12/16 (:OldRow.Phone_Number) "PL/SQL: OA-01031: insufficient privileges."

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Ok...figured out how to get more specific answers.
    Your mother would be proud of you!

    As good as I am with Oracle, I still can not debug code I can not see.
    Either you start using CUT & PASTE so I can see exactly what you are seeing, or you can resolve this without additional response from me.
    It's your choice.
    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.

  13. #13
    Join Date
    Dec 2009
    Posts
    20
    SQLPlus will not let me copy or paste using "CTRL + V" or left-clicking.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SQLPlus will not let me copy or paste using "CTRL + V" or left-clicking.
    Not SQLPLUSW (GUI), use sqlplus command line
    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.

  15. #15
    Join Date
    Dec 2009
    Posts
    20
    I am using the command line...I connected and then tried to paste my code in but the command line won't have it.

Tags for this Thread

Posting Permissions

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