ORA-04071 error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jay Krish
    New Member
    • Dec 2011
    • 1

    ORA-04071 error

    Hi
    I am having a error ora-04071
    Code:
    Error report:
    ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
    04071. 00000 -  "missing BEFORE, AFTER or INSTEAD OF keyword"
    *Cause:    The trigger statement is missing the BEFORE/AFTER/INSTEAD OF clause.
    *Action:   Specify either BEFORE, AFTER or INSTEAD OF.
    My Tables are :
    employees:

    Code:
    desc employees
    Name           Null     Type         
    -------------- -------- ------------ 
    EMPLOYEE_ID    NOT NULL NUMBER(6)    
    FIRST_NAME              VARCHAR2(20) 
    LAST_NAME      NOT NULL VARCHAR2(25) 
    EMAIL          NOT NULL VARCHAR2(25) 
    PHONE_NUMBER            VARCHAR2(20) 
    HIRE_DATE      NOT NULL DATE         
    JOB_ID         NOT NULL VARCHAR2(10) 
    SALARY                  NUMBER(8,2)  
    COMMISSION_PCT          NUMBER(2,2)  
    MANAGER_ID              NUMBER(6)    
    DEPARTMENT_ID           NUMBER(4)    
    LEFT_DATE               DATE         
    
    
     CREATE TABLE EMPLOYEE_SALARIES(
     EMPLOYEE_ID NUMBER NOT NULL,
     CHANGE_DATE DATE NOT NULL,
     SALARY NUMBER(8,2) NOT NULL,
     CONSTRAINT PK_EMPLOYEE_SALARIES PRIMARY KEY(EMPLOYEE_ID,CHANGE_DATE),
     CONSTRAINT FK_EMPLOYEE_SALARIES FOReiGN KEY(EMPLOYEE_ID)
     references employees(employee_id) on delete cascade);
     /
     
    Create Or Replace Trigger Maintain_Employee_Salaries
    
    for UPDATE OF SALARY
    ON EMPLOYEES 
    COMPOUND TRIGGER
    
    THRESHHOLD CONSTANT SIMPLE_INTEGER:=7;
    
    TYPE SALARIES_T IS TABLE OF  EMPLOYEE_SALARIES%ROWTYPE INDEX BY SIMPLE_INTEGER;
    SALARIES SALARIES_T;
    IDX SIMPLE_INTEGER :=0;
    
    PROCEDURE FLUSH_ARRAY 
    iS
          N Constant Simple_Integer:=Salaries.Count();
      Begin 
          Forall J In 1..N
            Insert Into Employee_Salaries Values Salaries(J);
            Salaries.Delete();
            Idx:=0;
            Dbms_Output.Put_Line('Flushed'||N||'row');
      End  Flush_Array;
       
        --after each row section
     After Each Row 
      is 
       Begin 
        Idx:=Idx+1;
        Salaries(Idx).Employee_Id :=:New.Employee_Id;
        Salaries(Idx).Change_Date:=Sysdate();
        Salaries(Idx).Salary:=:New.Salary;
          If  Idx>=Threshhold Then 
            Flush_Array();
          End If;
        end after each row;
        
     After Statement Is 
     Begin 
         Flush_Array();
     End After Statement;
    
    End Maintain_Salaries;
    Please help
    Thanks in advance
    Last edited by Stewart Ross; Jan 3 '12, 08:43 PM. Reason: Corrected code tagging
Working...