ora-04091,ORA-06512,ORA-04088 mutating ? 1

[oraprog]

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??

 

[Dagon]

And this is different to yesterday's question in what way exactly?

In a row-level trigger, you cannot insert, update or select from the table that the trigger has been created against. What you are trying to do is completely impossible with row-level triggers. You could try doing it with a statement level trigger.

For Oracle-related work, contact me through Linked-In.

 

[oraprog]

Thanks a lot
see
store record: 2001-10-10 2005-01-01 ok

when i insert new reocrd start_date & end_date
nested between store record start_date & end_date :::>>>

2001-10-10 ( 2002-01-30 - 2003-09-01 ) 2005-01-01I Need to separate the dates and the distribution on record for same emp_no :::>>>
emp_no start_date end_date
------- ---------- ----------
10 2001-10-10 2002-01-29 10 2002-01-30
10 2003-09-02 2005-01-01

note: 2002-01-29 = 2002-01-30 -1 days ok
and 2003-09-01 = 2003-09-01 + 1 day ok

note:
The possibility that emp_no multi record store before :
emp_no start_date end_date
------- ---------- ----------
10 1991-01-01 1999-12-30
10 2000-01-01 2001-10-09
10 2001-10-10 2002-01-29

I need when insert only one statment from 'form builder':>>
insert into hst(emp_no,start_date,end_date)
values(10 , 2002-01-30 , 2003-09-01);

I have become:
emp_no start_date end_date
------- ---------- ----------
10 1991-01-01 1999-12-30
10 2000-01-01 2001-10-09

10 2001-10-10 2002-01-29 #
10 2002-01-30 2003-09-01 *new insert by statment
10 2003-09-02 2005-01-01 **
# i need to update end_date Automatically
** insert this record Automatically

HOw???

 

[Dagon]

This might need a bit of work, but it shows you the principle of what you'd need to do to solve this using triggers.

SQL> drop table his
Table dropped.
SQL> create table his (emp_no number, start_date date, end_date date)
Table created.
SQL> create or replace package store_his is
  type t_his is table of his%rowtype index by pls_integer;
  hisUpd t_his;
end;
Package created.
SQL> create or replace trigger t_his_ar after insert on his for each row
declare
  nextEl PLS_INTEGER;
begin
  nextEl := store_his.hisUpd.COUNT+1;
  store_his.hisUpd(nextEl).emp_no := :new.emp_no;
  store_his.hisUpd(nextEl).start_date := :new.start_date;
  store_his.hisUpd(nextEl).end_date := :new.end_date;
end;
Trigger created.
SQL> create or replace trigger t_his_bs before insert on his
begin
  store_his.hisUpd.delete;
end;
Trigger created.
SQL> create or replace trigger t_his_As after insert on his
begin
  for i in nvl(store_his.hisUpd.first,1)..nvl(store_his.hisUpd.last,0) loop
    for rec in (select * 
                  from his 
                 where emp_no = store_his.hisUpd(i).emp_no 
                  and start_date <> store_his.hisUpd(i).start_date 
                  and store_his.hisUpd(i).end_date <> to_date('2005-01-01', 'yyyy-mm-dd')
                  and store_his.hisUpd(i).start_date between start_date and end_date) loop
      update his
         set end_date = store_his.hisUpd(i).start_date -1
       where emp_no = rec.emp_No
         and start_date = rec.start_date;
      --
      insert into his (emp_no, start_date, end_date) values (rec.emp_no, store_his.hisUpd(i).end_date, to_date('2005-01-01', 'YYYY-MM-DD'));  
    end loop;
  end loop;
end;
Trigger created.
SQL> insert into his values (10, to_date('2001-10-10', 'yyyy-mm-dd'), to_date('2005-01-01', 'yyyy-mm-dd'))
1 row created.
SQL> select * from his

    EMP_NO START_DATE END_DATE 
---------- ---------- ---------
        10 10-OCT-01  01-JAN-05
1 row selected.
SQL> insert into his values (10, to_date('2002-01-30', 'yyyy-mm-dd'), to_date('2003-09-01', 'yyyy-mm-dd'))
1 row created.
SQL> select * from his

    EMP_NO START_DATE END_DATE 
---------- ---------- ---------
        10 10-OCT-01  29-JAN-02
        10 30-JAN-02  01-SEP-03
        10 01-SEP-03  01-JAN-05

3 rows selected.

For Oracle-related work, contact me through Linked-In.

 

[oraprog]

THNKS A LOT ,

I need to dynamic code , no hard code ,
So that it works (trigger) when the repetition of the situation,

insert into his (emp_no, start_date, end_date)
values (rec.emp_no, store_his.hisUpd(i).end_date, to_date('2005-01-01', 'YYYY-MM-DD'));

 

[Dagon]

I don't know what you mean by that. I've made the script a bit more robust so that there is no chance of an infinite loop caused by the trigger inserts firing the trigger themselves.

drop table his;

create table his (emp_no number, start_date date, end_date date);

create or replace package store_his is
  type t_his is record
  (emp_no number,
   start_date date,
   end_date date,
   his_rowid rowid);
  type t_his_arr is table of t_his index by pls_integer; 
  hisUpd t_his_arr;
  trigInserts boolean := FALSE;
end;
/

create or replace trigger t_his_ar after insert on his for each row
declare
  nextEl PLS_INTEGER;
begin
  if not store_his.trigInserts then
    nextEl := store_his.hisUpd.COUNT+1;
    store_his.hisUpd(nextEl).emp_no := :new.emp_no;
    store_his.hisUpd(nextEl).start_date := :new.start_date;
    store_his.hisUpd(nextEl).end_date := :new.end_date;
    store_his.hisUpd(nextEl).his_rowid    := :new.rowid;
  end if;
end;
/

create or replace trigger t_his_bs before insert on his
begin
  if not store_his.trigInserts then
    store_his.hisUpd.delete;
  end if;
end;
/

create or replace trigger t_his_As after insert on his
begin
  store_his.trigInserts := TRUE;
  for i in nvl(store_his.hisUpd.first,1)..nvl(store_his.hisUpd.last,0) loop
    for rec in (select *
                  from his
                 where emp_no = store_his.hisUpd(i).emp_no
                  and rowid <> store_his.hisUpd(i).his_rowid
                  and store_his.hisUpd(i).start_date between start_date and end_date) loop
      update his
         set end_date = store_his.hisUpd(i).start_date -1
       where emp_no = rec.emp_No
         and start_date = rec.start_date;
      --
      insert into his (emp_no, start_date, end_date) values (rec.emp_no, store_his.hisUpd(i).end_date, to_date('2005-01-01', 'YYYY-MM-DD'));  
    end loop;
  end loop;
  store_his.trigInserts := FALSE;
end;
/

For Oracle-related work, contact me through Linked-In.

 

[oraprog]

Thanks a lot Dagon.
Thanks for your efforts,,,