assuming one record store in the table HST,
create table(emp_no,start_date,end_date)
primary_key(emp_no,start_date)
---------------------------------------::::
emp_no start_date end_date
------- ---------- ----------
10 2001-10-10 2005-01-01
-------------------------------------
and I insert new reocrd
*insert into hstemp_no,start_date,end_date)
values(10 , 2002-01-30 , 2003-09-01);
see nested within the time period stored in the table for same emp_no,
I need when insert such this case , store in table -->>
emp_no start_date end_date
------- ---------- ----------
10 2001-10-10 2002-01-29 old record i update end_date
10 2002-01-30 2003-09-01 *new insert by statment
10 2003-09-01 2005-01-01 this is record i need insert by trigger in same table
-------------------------------------
I write :
CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
UPDATE hst
SET END_DATE = MANS.P_DATE.SUBDATENEW.START_DATE,1)
WHERE EMP_NO
AND END_DATE >= :NEW.START_DATE
AND END_DATE <= :NEW.END_DATE
AND END_DATE IS NOT NULL;
INSERT INTO HST(EMP_NO,START_DATE,END_DATE)
SELECT EMP_NO, MANS.P_DATE.ADD_DATENEW.END_DATE,1) , END_DATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO
AND START_DATE <= :NEW.START_DATE
AND END_DATE >= :NEW.END_DATE
AND END_DATE = MANS.P_DATE.SUB30DATENEW.START_DATE,1) ;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/
-- COMMENT : MANS.P_DATE.SUBDATE Reduce its work one day
-- THE PROBLEM ISERT STATMENT IN TRIGGER,
-- AND GIVE THIS ERROR :
ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'
What I need to success trigger??