trigger in oracle

I have a source and a target table

The structure are as below:
 
--structure  of source table :

CREATE TABLE SHIFT_SUMMARY_NEW
(
SRNO NUMBER,
YEAR NUMBER(4),
MONTHS VARCHAR2(3),
DBA_NAME VARCHAR2(50),
SUN VARCHAR2(50),
MON VARCHAR2(50),
TUE VARCHAR2(50),
WED VARCHAR2(50),
THU VARCHAR2(50),
FRI VARCHAR2(50),
SAT VARCHAR2(50)
);


CREATE SEQUENCE SHIFT_SUMMARY_NEW_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

 CREATE OR REPLACE TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" 
  before insert on "SHIFT_SUMMARY_NEW"               
  for each row  
begin   
  if :NEW."SRNO" is null then 
    select "SHIFT_SUMMARY_NEW_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 
/
ALTER TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" ENABLE;



--structure of target table :

CREATE TABLE SHIFT_DETAILS 
(
ID NUMBER,
WORKING_DAY VARCHAR2(3),
WORK_DATE DATE,
DBA_NAME VARCHAR2(50),
SHIFT_TYPE VARCHAR2(50) 
);

CREATE SEQUENCE SHIFT_D_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER  "SHIFT_D_BR_I" 
  before insert on "SHIFT_DETAILS"               
  for each row  
BEGIN   
  if :NEW."ID" is null then 
    select "SHIFT_D_SEQ".nextval into :NEW."ID" from dual; 
  end if; 
end; 

ALTER TRIGGER  "SHIFT_D_BR_I" ENABLE

Open in new window



I want to write a trigger like when ever any new record is inserted then it should also populate the records as like the expected result in my earlier question :

http://www.experts-exchange.com/Database/Oracle/Q_26863370.html#a35034314
LVL 4
pinkurayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, you can do that.

Just have an AFTER INSERT trigger on the SHIFT_SUMMARY_NEW table and put the below code in the trigger.

INSERT into SHIFT_DETAILS
select to_char(to_date(months || '-' || year,'MON-YYYY') + (level-1) , 'DY') Day,
to_date(months || '-' || year,'MON-YYYY') + (level-1) work_date,
 dba_name,
 decode(to_char(to_date(months || '-' || year,'MON-YYYY') + (level-1) , 'DY'),
 'SUN',SUN,
 'MON',MON,
 'TUE',TUE,
 'WED',WED,
 'THU',THU,
 'FRI',FRI,
 'SAT',SAT) shift_type  
from dual , ( select * from shift_summary_new where dba_name =:new.dba_name)
connect by level <= ( select to_number(to_char(last_day(to_date(months || '-' || year,'MON-YYYY')),'DD')) from shift_summary_new
where dba_name =:new.dba_name ) ;

commit;

Test it out and let me know your feedback.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naveen KumarProduction Manager / Application Support ManagerCommented:
I think we will get a mutating error because we are trying to select from the same table shift_summary_new as part of the AFTER trigger execution.

So i would say have a job or something to check for the records in the shift_summary_new table and let this insert run for each new dba name there to populate the shift_details table.
Naveen KumarProduction Manager / Application Support ManagerCommented:
Even if use a PRAGMA autonomous_transaction in the trigger to avoid mutating error, then also the trigger option is not possible here because the record which we are inserting is not yet committed so this select statement "select * from shift_summary_new where dba_name =:new.dba_name" will not get any data and hence the insert i gave will not insert any records.

So i believe, the ONLY option we have is the below :
Have a job to check for the records in the shift_summary_new table on a frequency say ( every 1 hour or etc ) and let this insert run for each new dba name there to populate the shift_details table.

Thanks,
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pinkurayAuthor Commented:
Hi,

Please find the detail structure of my source and target table :

 
/*MASTER TABLE OR SOURCE TABLE STRUCTURE*/
  CREATE TABLE "MGMT"."SHIFT_SUMMARY" 
   (	"SRNO" NUMBER, 
	"YEAR" NUMBER(4,0), 
	"MONTH" VARCHAR2(3 BYTE), 
	"DBA" VARCHAR2(50 BYTE), 
	"SUN" VARCHAR2(50 BYTE), 
	"MON" VARCHAR2(50 BYTE), 
	"TUE" VARCHAR2(50 BYTE), 
	"WED" VARCHAR2(50 BYTE), 
	"THU" VARCHAR2(50 BYTE), 
	"FRI" VARCHAR2(50 BYTE), 
	"SAT" VARCHAR2(50 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 

  CREATE OR REPLACE TRIGGER "MGMT"."SHIFT_SUMMARY_AR_I" 
    AFTER INSERT
        ON SHIFT_SUMMARY
        FOR EACH ROW

    BEGIN
        -- Insert record into SHIFT_DETAILS table
INSERT into SHIFT_DETAILS(WORKING_DAY,WORK_DATE,DBA,SHIFT_TYPE)
select to_char(to_date(MONTH || '-' || year,'MON-YYYY') + (level-1) , 'DY') Day,
to_date(MONTH || '-' || year,'MON-YYYY') + (level-1) work_date,
 dba,
 decode(to_char(to_date(MONTH || '-' || year,'MON-YYYY') + (level-1) , 'DY'),
 'SUN',SUN,
 'MON',MON,
 'TUE',TUE,
 'WED',WED,
 'THU',THU,
 'FRI',FRI,
 'SAT',SAT) shift_type  
from dual , ( select * from shift_summary where dba =:new.dba)
connect by level <= ( select to_number(to_char(last_day(to_date(MONTH || '-' || year,'MON-YYYY')),'DD')) from shift_summary
where dba =:new.dba ) ;

commit;
 END;
/
ALTER TRIGGER "MGMT"."SHIFT_SUMMARY_AR_I" ENABLE;
 

  CREATE OR REPLACE TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" 
  before insert on "SHIFT_SUMMARY"               
  for each row  
begin   
  if :NEW."SRNO" is null then 
    select "SHIFT_SUMMARY_NEW_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 
/
ALTER TRIGGER "MGMT"."SHIFT_MASTER_NEW_BR_I" ENABLE;
 
/* TARGET TABLE STRUCTURE */


  CREATE TABLE "MGMT"."SHIFT_DETAILS" 
   (	"SRNO" NUMBER, 
	"WORKING_DAY" VARCHAR2(3 BYTE), 
	"WORK_DATE" DATE, 
	"DBA" VARCHAR2(50 BYTE), 
	"SHIFT_TYPE" VARCHAR2(50 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 

  CREATE OR REPLACE TRIGGER "MGMT"."SHIFT_D_BR_I" 
  before insert on "SHIFT_DETAILS"               
  for each row  
BEGIN   
  if :NEW."SRNO" is null then 
    select "SHIFT_D_SEQ".nextval into :NEW."SRNO" from dual; 
  end if; 
end; 
/
ALTER TRIGGER "MGMT"."SHIFT_D_BR_I" ENABLE;

Open in new window


For testing this here is how we need to insert a record into SHIFT_SUMMARY table which should fire the trigger to load the data into SHIFT_DETAILS table:

INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TUE,WED,THU,FRI,SAT)
VALUES('2011','MAR','PINKURAY','WEEK OFF','WEEK OFF','IST','PST','EST','EST','IST');

But when I execute this insert statement it give me the error as below:


 
SQL Error: ORA-04091: table MGMT.SHIFT_SUMMARY is mutating, trigger/function may not see it
ORA-06512: at "MGMT.SHIFT_SUMMARY_AR_I", line 3
ORA-04088: error during execution of trigger 'MGMT.SHIFT_SUMMARY_AR_I'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Open in new window

Naveen KumarProduction Manager / Application Support ManagerCommented:
yes i know that. please check my updates : 35034798 and 35034839
pinkurayAuthor Commented:
can you send me the modified code for the trigger on PRAGMA autonomous_transaction?
Naveen KumarProduction Manager / Application Support ManagerCommented:
even that does not work. i have tested it and i have already updated in the update 35034839 as to why that does not work.

It is not just that. i also tried to put this insert into procedure with autonomous and then call the procedure from the trigger but again it boils down to the update given in 35034839

In simple terms, before the trigger execution gets completed, we will not be able to select the new data from that table itself ( irrespective of whether we use a procedure or trigger with autonomous etc )
pinkurayAuthor Commented:
So then what will be the solution for this ?
Naveen KumarProduction Manager / Application Support ManagerCommented:
i am not sure whether you read my update 35034839 or not ? Just copying again here......

So i believe, the ONLY option we have is the below :
Have a job to check for the records in the shift_summary_new table on a frequency say ( every 1 hour or etc ) and let this insert run for each new dba name there to populate the shift_details table.
pinkurayAuthor Commented:
yes got it did you created any job and tested it?
or you want me to open another question for a job?
Naveen KumarProduction Manager / Application Support ManagerCommented:
no need of another question. but it will take a day or two for me to give you that.
pinkurayAuthor Commented:
if we can get some logic to view if two records present in SHIFT_SUMMARY table then I think we can solve this.

Let assume we insert 2 records like :

INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TUE,WED,THU,FRI,SAT)
VALUES('2011','MAR','PINKURAY','IST','EST','WEEK OFF','WEEK OFF','EST','EST','IST');

INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TUE,WED,THU,FRI,SAT)
VALUES('2011','MAR','JOHN','WEEK OFF','WEEK OFF','IST','EST','EST','EST','IST');

If I have only one record , lets take  PINKURAY then I can view the expected result by querying the below:

 
select to_char(to_date(month || '-' || year,'MON-YYYY') + (level-1) , 'DY') Day,
TO_DATE(MONTH|| '-' || YEAR,'MON-YYYY') + (LEVEL-1) WORK_DATE,
 shift_summary.DBA,
 decode(to_char(to_date(MONTH || '-' || year,'MON-YYYY') + (level-1) , 'DY'),
 'SUN',SUN,
 'MON',MON,
 'TUE',TUE,
 'WED',WED,
 'THU',THU,
 'FRI',FRI,
 'SAT',SAT) SHIFT_TYPE  
FROM DUAL , MGMT.SHIFT_SUMMARY
CONNECT BY LEVEL <= ( SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(MONTH || '-' || YEAR,'MON-YYYY')),'DD')) FROM mgmt.SHIFT_SUMMARY )

Open in new window



But now if I insert 2 rows in to the shift_summary table with JOHN's data then the above query doesn't work.

Now can we find for result for both ?
pinkurayAuthor Commented:
I made a procedure in place of a trigger which worked perfectly for my solution. thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.