Solved

trigger in oracle

Posted on 2011-03-04
13
642 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:pinkuray
  • 7
  • 6
13 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 35034425
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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034798
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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034839
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,
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034902
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

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034910
yes i know that. please check my updates : 35034798 and 35034839
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35034948
can you send me the modified code for the trigger on PRAGMA autonomous_transaction?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35034995
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 )
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35035176
So then what will be the solution for this ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35035184
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.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35035218
yes got it did you created any job and tested it?
or you want me to open another question for a job?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35035256
no need of another question. but it will take a day or two for me to give you that.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35035349
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 ?
0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35037981
I made a procedure in place of a trigger which worked perfectly for my solution. thanks a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now