Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: Problem with Triggers(Compilation Error)

    I have First created two tables :
    CL_FACULTY_RATE
    (
    EMP_ID NUMBER,
    LOG_TYPE_ID NUMBER,
    PAY_RATE NUMBER NOT NULL,
    WEF_DATE DATE NOT NULL,
    EMP_ID_UPD NUMBER NOT NULL,
    RECORD_DATE DATE NOT NULL
    ) and
    CL_FACULTY_RATE_HIST
    (
    EMP_ID NUMBER,
    LOG_TYPE_ID NUMBER,
    PAY_RATE NUMBER NOT NULL,
    WEF_DATE DATE NOT NULL,
    EMP_ID_UPD NUMBER NOT NULL,
    RECORD_DATE DATE NOT NULL
    )

    Now I have written a trigger which on updation in any of the row in the first table , inserts the old row into the second table ti mentain history...
    Trigger is:
    CREATE TRIGGER trigOnUpdation
    AFTER UPDATE ON CL_FACULTY_RATE
    REFERENCING NEW AS newRow OLD AS oldRow
    FOR EACH ROW
    BEGIN
    INSERT INTO CL_FACULTY_RATE_HIST VALUES(ldRow.EMP_ID,ldRow.LOG_TYPE_ID,
    ldRow.PAY_RATE,ldRow.WEF_DATE,ldRow.EMP_ID_UPD,ldRow.RECORD_DATE,ldRow.LOGGED_DATE);
    END trigOnUpdation;


    But it is showing :- Error on compilation
    When I give show errors command the output is : No errors

    So, when I try to update any row in first table I get an error as:"ORA-04098: trigger 'SCOTT.TRIGONUPDATION' is invalid and failed re-validation"
    So please suggest : what is the problem and what this error says...

    Thanx in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Oracle
    ORA-04098: trigger 'string.string' is invalid and failed re-validation

    Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.

    Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
    Here is a working example; try to compare it (or even test on your Scott's schema) with your solution. As of 'SHOW ERRORS', see what "my" syntax says with your code and post the result back here.
    Code:
    SQL> create table dept_hist as select * From dept where 1 = 2;
    
    Table created.
    
    SQL> create trigger trg_dept_hist
      2    before update on dept
      3    for each row
      4  begin
      5    insert into dept_hist
      6      (deptno, dname, loc)
      7       values
      8      (:old.deptno, :old.dname, :old.loc);
      9  end;
     10  /
    
    Trigger created.
    
    SQL> show errors trigger trg_dept_hist;     --> try with this syntax!
    No errors.
    
    SQL> update dept set loc = 'NY' where deptno = 10;
    
    1 row updated.
    
    SQL> select * from dept_hist;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    First of all , thanx for ur reply...
    The above code that you have given is working fine.I got an help from the code that you have provided as I compared the fields of original and backup table and I found there is one more field in the history(backup) table.That is why trigger is giving the compilation error.

    But show error command is not detecting the problem,I don't know why?
    But the problem is solved.
    Thanx once again

Posting Permissions

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