trigger in oracle
I have a source and a target table
The structure are as below:
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 :
https://www.experts-exchange.com/questions/26863370/Pivoting-in-SQL-using-the-10g.html?anchorAnswerId=35034314#a35034314
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" ENABLEI 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 :
https://www.experts-exchange.com/questions/26863370/Pivoting-in-SQL-using-the-10g.html?anchorAnswerId=35034314#a35034314
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,
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,
ASKER
Hi,
Please find the detail structure of my source and target table :
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,TU
VALUES('2011','MAR','PINKU
But when I execute this insert statement it give me the error as below:
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;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,TU
VALUES('2011','MAR','PINKU
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.
yes i know that. please check my updates : 35034798 and 35034839
ASKER
can you send me the modified code for the trigger on PRAGMA autonomous_transaction?
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 )
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 )
ASKER
So then what will be the solution for this ?
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.
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.
ASKER
yes got it did you created any job and tested it?
or you want me to open another question for a job?
or you want me to open another question for a job?
no need of another question. but it will take a day or two for me to give you that.
ASKER
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,TU
VALUES('2011','MAR','PINKU
INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TU
VALUES('2011','MAR','JOHN'
If I have only one record , lets take PINKURAY then I can view the expected result by querying the below:
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 ?
Let assume we insert 2 records like :
INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TU
VALUES('2011','MAR','PINKU
INSERT INTO SHIFT_SUMMARY (YEAR,MONTH,DBA,SUN,MON,TU
VALUES('2011','MAR','JOHN'
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 )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 ?
ASKER
I made a procedure in place of a trigger which worked perfectly for my solution. thanks a lot
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.