Solved

Error: ORA-04079: invalid trigger specification

Posted on 2011-03-07
32
2,596 Views
Last Modified: 2012-05-11
I am trying to write a trigger with autonomous transaction when once row is inserted into one table it should also insert into the other table with more 31 records as per the month.

So means one records will be inserted into main table and then it should calculate for rest of the month and insert into the 2nd table:

I was trying to create a trigger, below is my trigger code but while compiling my trigger I got this error "Error: ORA-04079: invalid trigger specification" :



 
CREATE OR REPLACE
TRIGGER SHIFT_SUMMARY_AR_I AFTER
  INSERT ON SHIFT_SUMMARY FOR EACH ROW 
  COMPOUND TRIGGER

  DECLARE
pragma autonomous_transaction;

CURSOR C1 IS( SELECT TO_CHAR(to_date(month
    || '-'
    || YEAR,'MON-YYYY') + (level-1) , 'DY') WORKING_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
       ));
  BEGIN
  FOR IREC IN C1
  LOOP
  -- Insert record into SHIFT_DETAILS table
  INSERT
  INTO SHIFT_DETAILS
    (
      WORKING_DAY,
      WORK_DATE,
      DBA,
      SHIFT_TYPE
    )
  VALUES(
  C1.WORKING_DAY,
  C1.WORK_DATE,
  C1.DBA,
  C1.SHIFT_TYPE
  )
       ;
       END LOOP;
     COMMIT;
  END;

Open in new window

0
Comment
Question by:pinkuray
  • 20
  • 8
  • 4
32 Comments
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 35064395
the syntax for using compound trigger is incorrect. Please find the link which gives the syntax on how to use them :

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD

why do you want to use compound trigger ? any specific reason ?
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35064534
No its just that I want a trigger to be implemented:

if I remove compound trigger , I get these below errors:

Error(30,3): PL/SQL: SQL Statement ignored
Error(42,3): PLS-00225: subprogram or cursor 'C1' reference is out of scope
Error(42,6): PL/SQL: ORA-00984: column not allowed here

The functionality should be something when ever I insert one record in main table it should calculate the whole month and insert 31 records into other table if it is for month march.

As we have a api in Apex whcih will load multiple records into main table , so if a trigger is been implemented on that then it should also insert records to next table with multiple rows
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35065816
@nav_kum_v:

What do you suggest on this?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35067005
i think even if you compile it, this is not going to work for you. i have posted why it cannot work in the last question.

Also for ease of use and understanding, first of all do not use oracle keywords for your column names like DBA, MONTH etc.. use it like DBA_NAME, MONTH_NAME..
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35067130
I changed the name to DBA_NAME, MONTH_NAME ..


Still no luck:  

If I write a procedure for one record it is doing my work but my issue is i am going to load bulk records into shift_summary table where i also need to load it to shift_details table with monthly calculation.

can you help me in this
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35067147
simple right, call the procedure in a loop for each record in the shift_summary table AFTER THE RECORD GETS INSERTED INTO THE TABLE. Do not try to mix it with the trigger because when the trigger is in execution, we will not get any record if we query the table shift_summary for the new record you are inserting.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35067170
here is the procedure last time i used for one record can you modify it as what you said? :

 
create or replace
PROCEDURE p_ishift_sd
  (
    V_YEAR  IN NUMBER ,
    V_MONTH IN VARCHAR2 ,
    V_DBA   IN VARCHAR2 ,
    V_SUN   IN VARCHAR2 ,
    V_MON   IN VARCHAR2 ,
    V_TUE   IN VARCHAR2 ,
    V_WED   IN VARCHAR2 ,
    V_THU   IN VARCHAR2 ,
    V_FRI   IN VARCHAR2 ,
    V_SAT   IN VARCHAR2)
IS
BEGIN
  INSERT
  INTO SHIFT_SUMMARY
    (
      YEAR,
      MONTH_NAME,
      DBA_NAME,
      SUN,
      MON,
      TUE,
      WED,
      THU,
      FRI,
      SAT
    )
    VALUES
    (
      V_YEAR,
      V_MONTH,
      V_DBA,
      V_SUN,
      V_MON,
      V_TUE,
      V_WED,
      V_THU,
      V_FRI,
      V_SAT
    );
  INSERT INTO SHIFT_DETAILS
    (WORKING_DAY,WORK_DATE,DBA ,SHIFT_TYPE
    )
  SELECT TO_CHAR(to_date(V_MONTH
    || '-'
    || V_YEAR,'MON-YYYY') + (level-1) , 'DY') DAY,
    TO_DATE(V_MONTH
    || '-'
    || V_YEAR,'MON-YYYY') + (LEVEL-1) WORK_DATE,
    shift_summary.DBA,
    DECODE(TO_CHAR(to_date(V_MONTH
    || '-'
    || V_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_NAME = V_DBA
    ) SHIFT_SUMMARY
    CONNECT BY LEVEL <=
    (SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MONTH
      || '-'
      || V_YEAR,'MON-YYYY')),'DD'))
    FROM SHIFT_SUMMARY
    WHERE DBA_NAME=V_DBA
    );
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35067175
but now as we have a application which will load multiple records into shift_summary table so my procedure is not going to work...

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35067219
have one more column in the shift_summary table to differentiate which one we have already processed into shift_details table and which are not.

processed varchar2(1)

declare
cursor c1 is select * from shift_summary where processed = 'N';
begin
for x in c1
loop
p_ishift_sd(x.col1, x.col2, ... );
update shift_summary set processed = 'Y' where col1 = x.col1;
commit;
end loop;
end;
/

change col1, col2 to actual column names accordingly. If you execute this block, then it will take care of inserting into shift_details table
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35067870
I have SRNO which is a sequence in shift_summary table but this is not there in detail table
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35067904
I have SRNO which is a sequence in shift_summary table but this is not there in detail table  ---> how does this relate to this question. i cannot understand what you are trying to say.
0
 

Expert Comment

by:varnierd
ID: 35068298
Hi, the problem in the trigger is that you try to reselect the data from the table

try to modify your trigger like this  
 
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :new.dba,
                  DECODE (
                     TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'),
                     'SUN', SUN,
                     'MON', MON,
                     'TUE', TUE,
                     'WED', WED,
                     'THU', THU,
                     'FRI', FRI,
                     'SAT', SAT)
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:new.month || '-' || :new.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (C1.WORKING_DAY,
                   C1.WORK_DATE,
                   C1.DBA,
                   C1.SHIFT_TYPE);
   END LOOP;
END;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068440
After executing your trigger got the below erroes:

Error(10,7): PL/SQL: SQL Statement ignored
Error(27,29): PL/SQL: ORA-00904: "SAT": invalid identifier
Error(42,7): PL/SQL: SQL Statement ignored
Error(49,20): PLS-00225: subprogram or cursor 'C1' reference is out of scope
Error(49,23): PL/SQL: ORA-00984: column not allowed here


0
 

Expert Comment

by:varnierd
ID: 35068753
The problem was there, what do you want to do with this decode?
DECODE (
                     TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'),
                     'SUN', SUN,
                     'MON', MON,
                     'TUE', TUE,
                     'WED', WED,
                     'THU', THU,
                     'FRI', FRI,
                     'SAT', SAT)

remove the dot ?
You can do this with:
replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '')
instead


0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068754

>> have one more column in the shift_summary table to differentiate which one we have already processed into shift_details table and which are not.

@nav_kum_v: The table can only be differed as we are calculating for the month, and SRNO is the primary key for shift summary table which is auto generated for each record.
Where as this not refereed in shift_details , so SRNO cannot be used as reference to identify which records are been processed.

The only way to identify is based on DBA+ MONTH+YEAR column, boz these information are also stored in details table.

Hope now I answered your query..
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068806
Still same error:

Error(33,7): PL/SQL: SQL Statement ignored
Error(40,20): PLS-00225: subprogram or cursor 'C1' reference is out of scope
Error(40,23): PL/SQL: ORA-00984: column not allowed here

@varnierd: attached is the modified code as per you  
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :new.dba,
                  replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:new.month || '-' || :new.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (C1.WORKING_DAY,
                   C1.WORK_DATE,
                   C1.DBA,
                   C1.SHIFT_TYPE);
   END LOOP;
END;

Open in new window

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.

 

Expert Comment

by:varnierd
ID: 35068861
Yes there is another error in syntax of trigger:
 
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :new.dba,
                  replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:new.month || '-' || :new.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (C1.WORKING_DAY,
                   C1.WORK_DATE,
                   C1.DBA,
                   C1.SHIFT_TYPE);
   END LOOP;
END;
END;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068883
My shift summary record is like the below if one single record inserted :

YEAR      MONTH      DBA      SUN      MON      TUE      WED      THU      FRI      SAT
2011      MAR            PINKURAY      OFF      OFF      IST      PST      EST      EST      IST

Any time this record get inserted into summary table, the detail table should contain the whole month shift like

If you create a table as like shift summary and then run this below query then hope can understand what I need when any record get inserted into summary table the detail table should insert 31 records.
like the result of this below query :

 
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 ,
    (SELECT * FROM SHIFT_SUMMARY WHERE DBA = 'JOHN'
    ) SHIFT_SUMMARY
    CONNECT BY LEVEL <=
    (SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE( MONTH
      || '-'
      ||  YEAR,'MON-YYYY')),'DD'))
    FROM SHIFT_SUMMARY)
   ;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068939
same error:

Error(34,7): PL/SQL: SQL Statement ignored
Error(41,20): PLS-00225: subprogram or cursor 'C1' reference is out of scope
Error(41,23): PL/SQL: ORA-00984: column not allowed here

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35068959
@varnierd: After running the code posted : on 03/08/11 05:06 AM, ID: 35068861 ,have the same error no change on that.




0
 
LVL 4

Author Comment

by:pinkuray
ID: 35069245
I change the trigger slightly and Compiled  uccessfully:

Here is the modified code:

 
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (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 ,
    (SELECT * FROM SHIFT_SUMMARY WHERE DBA = :new.DBA
    ) SHIFT_SUMMARY
    CONNECT BY LEVEL <=
    (SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE( MONTH
      || '-'
      ||  YEAR,'MON-YYYY')),'DD'))
    FROM SHIFT_SUMMARY));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (IREC.DAY,
                   IREC.WORK_DATE,
                   IREC.DBA,
                   IREC.SHIFT_TYPE);
   END LOOP;
END;
END;

Open in new window


When I did a insert now I got this error:

SQL Error: ORA-04091: table SR.SHIFT_SUMMARY is mutating, trigger/function may not see it
ORA-06512: at "SR.SHIFT_SUMMARY_AR_I", line 5
ORA-06512: at "SR.SHIFT_SUMMARY_AR_I", line 24
ORA-04088: error during execution of trigger 'SR.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.


SR is my schema name..

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35069274
Even changed the trigger and compiled successfully still the same mutating error:

 
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (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,
   replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
  FROM DUAL ,
    (SELECT * FROM SHIFT_SUMMARY WHERE DBA = :new.DBA
    ) SHIFT_SUMMARY
    CONNECT BY LEVEL <=
    (SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE( MONTH
      || '-'
      ||  YEAR,'MON-YYYY')),'DD'))
    FROM SHIFT_SUMMARY));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (IREC.DAY,
                   IREC.WORK_DATE,
                   IREC.DBA,
                   IREC.SHIFT_TYPE);
   END LOOP;
END;
END;

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35069364
i have told many times that do not mix up this with the trigger as it is not going to work.

simple right, call the procedure in a loop for each record in the shift_summary table AFTER THE RECORD GETS INSERTED INTO THE TABLE. Do not try to mix it with the trigger because when the trigger is in execution, we will not get any record if we query the table shift_summary for the new record you are inserting.
0
 

Accepted Solution

by:
varnierd earned 400 total points
ID: 35069563
You take the version with the select on the shift_summary table..

Here is the good one

 
CREATE OR REPLACE TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :new.dba,
                  replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:new.month || '-' || :new.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (C1.WORKING_DAY,
                   C1.WORK_DATE,
                   C1.DBA,
                   C1.SHIFT_TYPE);
   END LOOP;
END;
END;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35069824
@varnierd: after executing your trigger it didn't worked for.

Got this error:
Error(34,7): PL/SQL: SQL Statement ignored
Error(41,20): PLS-00225: subprogram or cursor 'C1' reference is out of scope
Error(41,23): PL/SQL: ORA-00984: column not allowed here


@nav_kum_v: I think this is possible.

Sending the solution next a trigger which solved my problem. Of-course without all your input this was not possible at all...

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35069881
@nav_kum_v & @varnierd:

Hope you don't mind if I take a few points for my question.

Please confirm  on this..if you have any objecting then I give the points to varnierd.  

Below attached is the correct trigger which works perfect for me.

 
create or replace
TRIGGER SHIFT_SUMMARY_AR_I
   AFTER INSERT
   ON SHIFT_SUMMARY
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :new.dba AS DBA_NEW,
                  replace(TO_CHAR (
                        TO_DATE (:new.month || '-' || :new.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:new.month || '-' || :new.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- Insert record into SHIFT_DETAILS table
      INSERT INTO SHIFT_DETAILS (WORKING_DAY,
                                 WORK_DATE,
                                 DBA,
                                 SHIFT_TYPE)
           VALUES (IREC.WORKING_DAY,
                   IREC.WORK_DATE,
                   IREC.DBA_NEW,
                   IREC.SHIFT_TYPE);
   END LOOP;
END;
END;

Open in new window

0
 
LVL 4

Author Closing Comment

by:pinkuray
ID: 35069964
Just shared the points between both of you but
The correct code is in post :
03/08/11 07:09 AM, ID: 35069881

varnierd you deserved more points, hope nav will never mind :-).....
I cannot take any points .. as this was my question, I was just kidding boz you guys deserve it.

This is really great time when you finally get the solution.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35075811
i am fine in points going to anyone here because the fact still remains the same that we cannot query the table when the trigger is in execution.
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35076080
True :-)
0
 
LVL 4

Author Comment

by:pinkuray
ID: 35076769
Also updating with a new trigger which will track if any modification are done on master table then the same should also be reflected on detail table:

Thought of sharing this.

Below is the trigger for any update on master table to reflect the same with detail table:

 
create or replace
TRIGGER SHIFT_SUMMARY_AR_U
   AFTER UPDATE 
   ON SHIFT_SUMMARY
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
BEGIN
DECLARE
   CURSOR C1
   IS
      (    SELECT TO_CHAR (
                     TO_DATE (:NEW.month || '-' || :NEW.YEAR, 'MON-YYYY') + (LEVEL - 1),
                     'DY')
                     WORKING_DAY,
                  TO_DATE (:NEW.month || '-' || :NEW.YEAR, 'MON-YYYY') + (LEVEL - 1)
                     WORK_DATE,
                  :NEW.dba AS DBA_NEW,
                  replace(TO_CHAR (
                        TO_DATE (:NEW.month || '-' || :NEW.YEAR, 'MON-YYYY') + (LEVEL - 1),
                        'DY'), '.', '') 
                     shift_type
             FROM DUAL
       CONNECT BY LEVEL <=
                     (SELECT TO_NUMBER (
                                TO_CHAR (
                                   LAST_DAY (
                                      TO_DATE (:NEW.month || '-' || :NEW.YEAR,
                                               'MON-YYYY')),
                                   'DD'))
                        FROM DUAL));
BEGIN
   FOR IREC IN C1
   LOOP
      -- update record into SHIFT_DETAILS table
      UPDATE SHIFT_DETAILS 
      SET WORKING_DAY =IREC.WORKING_DAY,
        WORK_DATE =IREC.WORK_DATE,
          DBA = IREC.DBA_NEW,
         SHIFT_TYPE=IREC.SHIFT_TYPE 
         WHERE DBA=:OLD.DBA ;
   END LOOP;
END;
END;

Open in new window

0
 
LVL 4

Author Comment

by:pinkuray
ID: 35079176
Also a procedure which can be used for the same functionality to insert records into detail table from master:

 
create or replace procedure insert_shift_details( P_month IN VARCHAR2, P_YEAR IN NUMBER)
as
v_monthyear varchar2(11);
v_first_day date;
v_last_day date;
v_current_date date;
cursor c1 is select * from shift_summary
where month = p_month and year = p_year;
v_shift VARCHAR2(50);
v_current_day VARCHAR2(10);
BEGIN
for i in c1 loop
   v_monthyear := '01-'||i.month||'-'||i.year;
   v_first_day := to_date(v_monthyear);
   v_last_day := last_day(v_first_day);

   for j in 0..(v_last_day - v_first_day) loop

      v_current_date := v_first_day + j;
      v_shift := NULL;
      v_current_day := TRIM(to_char(v_current_date,'DAY'));
      IF  v_current_day = 'MONDAY' then
          v_shift := i.MON;
      dbms_output.put_line('MON: '||i.MON);
      ELSIF v_current_day = 'TUESDAY' then
          v_shift := i.S_TUE;
      ELSIF v_current_day = 'WEDNESDAY' then
          v_shift := i.S_WED;
      ELSIF v_current_day = 'THURSDAY' then
          v_shift := i.S_THU;
      ELSIF v_current_day = 'FRIDAY' then
          v_shift := i.S_FRI;
      dbms_output.put_line('FRI: '||i.S_FRI);
      ELSIF v_current_day = 'SATURDAY' then
          v_shift := i.S_SAT;
      ELSIF v_current_day = 'SUNDAY' then
          v_shift := i.S_SUN;
      END IF;

      insert into shift_details (work_date, dba, shift_type) values (v_current_date,i.dba,v_shift);

   end loop;
end loop;
END;

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35079292
Good
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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

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