Hi
I am having a error ora-04071
My Tables are :
employees:
Please help
Thanks in advance
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.
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;
Thanks in advance