Home » SQL & PL/SQL » SQL & PL/SQL » Trigger problem (oracle 10g)
Trigger problem [message #445004] Thu, 25 February 2010 03:36 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or replace TRIGGER TR_INSERT_GROUPMASTER AFTER INSERT ON EMRGROUPMASTER  
FOR EACH ROW 
DECLARE
 CURSOR ezEMRxScreenImmunRecords IS SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 AND MASTER_REFERENCE_ID IS NULL
           ORDER BY SCREEN_IMMUN_ID; 
  currentGroupID  NUMBER(20,0);      
  currentScreenImmunID NUMBER(20,0);
  currentScreenImmunRecordType VARCHAR2(2);
  seedScreenImmunID NUMBER(20,0);
  updateQuery1 VARCHAR2(4000);
  propertyName VARCHAR2(50);
  screenImmunRecord EMRScreenImmunLkup%ROWTYPE;
BEGIN
   SELECT ezEMRxID INTO currentScreenImmunID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';    
      OPEN ezEMRxScreenImmunRecords;
           LOOP
               FETCH ezEMRxScreenImmunRecords INTO screenImmunRecord;
	           EXIT WHEN ezEMRxScreenImmunRecords%NOTFOUND;  
                 screenImmunRecord.MASTER_REFERENCE_ID := screenImmunRecord.SCREEN_IMMUN_ID;
                 screenImmunRecord.SCREEN_IMMUN_ID := currentScreenImmunID; 
                 INSERT INTO EMRSCREENIMMUNLKUP VALUES screenImmunRecord;
                 currentScreenImmunID := currentScreenImmunID + 1;
            END LOOP;
       CLOSE ezEMRxScreenImmunRecords;
        updateQuery1 := 'UPDATE EMRIDS SET EZEMRXID = ' ||  TO_CHAR(currentScreenImmunID) || ' WHERE PROPERTY_NAME = ''' || propertyName || '''';
           execute immediate updateQuery1;
END;
/




hi when i insert row into emrgroupmaster with group_id column as 1501 it is not inserting group_id column in emscreenimmunlkup as
1501 for all available rows.and if i insert only one record it is
allowing me.more than on row insertion in trigger failing
Re: Trigger problem [message #445012 is a reply to message #445004] Thu, 25 February 2010 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any error to share with us?

Regards
Michel
Re: Trigger problem [message #445013 is a reply to message #445012] Thu, 25 February 2010 04:02 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes

INSERT INTO EMRGROUPMASTER VALUES (1502,'DEV',1,NULL,'GG01')

where 1501 is group_id and not available in the table at all


Error starting at line 1 in command:
INSERT INTO EMRGROUPMASTER VALUES (1502,'DEV',1,NULL,'GG01')
Error report:
SQL Error: ORA-00001: unique constraint (CLINICALRELEASE.EMRSCREENIMMUNLKUP_PK) violated
ORA-06512: at "CLINICALRELEASE.TR_INSERT_GROUPMASTER", line 26
ORA-04088: error during execution of trigger 'CLINICALRELEASE.TR_INSERT_GROUPMASTER'
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Re: Trigger problem [message #445016 is a reply to message #445013] Thu, 25 February 2010 04:13 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hhi for me group_id in emrgroupmaster if i insert 1501 in it is not coming and inserted only for that pariticluar column in emrscreenimmunlkup as it is coming for zero.

and when i drooped my promary key constraint on table emrscreenimmunlkup it is allowing me to insert records.
Re: Trigger problem [message #445018 is a reply to message #445016] Thu, 25 February 2010 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So is it or is it not a primary key?
If it is you have to fix your code.
If it is not then it is fine.

Regards
Michel
Re: Trigger problem [message #445019 is a reply to message #445004] Thu, 25 February 2010 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not at all clear what you doing.
1.always specify the column list in an insert statement. We have no idea what values are being inserted into which columns.
2. What column(s) is the primary key in EMRSCREENIMMUNLKUP (I assume that's the one being violated) on?
3. Why are you using dynamic sql? There is nothing dynamic about that update statement, make it static.
Re: Trigger problem [message #445036 is a reply to message #445019] Thu, 25 February 2010 05:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi for me only problem was my group_id value inserted in emrgroupmaster table was not coming into emrscreenimmunlkup table.There only am struggling
Re: Trigger problem [message #445037 is a reply to message #445004] Thu, 25 February 2010 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And since you've avoided using column names in most of that code we can't tell where the value should be coming from.
Re: Trigger problem [message #445038 is a reply to message #445037] Thu, 25 February 2010 06:05 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or replace
TRIGGER TR_INSERT_GROUPMASTER AFTER INSERT ON EMRGROUPMASTER  
FOR EACH ROW 
DECLARE
 CURSOR ezEMRxScreenImmunRecords IS 
    SELECT * FROM EMRSCREENIMMUNLKUP 
           WHERE GROUP_ID = 0 AND MASTER_REFERENCE_ID IS NULL
           ORDER BY SCREEN_IMMUN_ID; 
  currentGroupID  NUMBER(20,0);      
  currentScreenImmunID NUMBER(20,0);
  --currentScreenImmunMasterID NUMBER(20,0);
  currentScreenImmunRecordType VARCHAR2(2);
  seedScreenImmunID NUMBER(20,0);
  updateQuery1 VARCHAR2(4000);
  propertyName VARCHAR2(50);
  screenImmunRecord EMRScreenImmunLkup%ROWTYPE;
  v_group_id EMRGROUPMASTER.Group_id%type;
  BEGIN
    SELECT GROUP_ID INTO v_group_id FROM EMRGROUPMASTER;
    SELECT ezEMRxID INTO currentScreenImmunID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';    
      OPEN ezEMRxScreenImmunRecords;
		          LOOP
               FETCH ezEMRxScreenImmunRecords INTO screenImmunRecord;
			         EXIT WHEN ezEMRxScreenImmunRecords%NOTFOUND;  
                 screenImmunRecord.MASTER_REFERENCE_ID := screenImmunRecord.SCREEN_IMMUN_ID;
                 screenImmunRecord.SCREEN_IMMUN_ID := currentScreenImmunID; 
                 screenImmunRecord.GROUP_ID := v_group_id;
                       INSERT INTO EMRSCREENIMMUNLKUP VALUES screenImmunRecord;
                 currentScreenImmunID := currentScreenImmunID + 1;
            END LOOP;
           CLOSE ezEMRxScreenImmunRecords;
        updateQuery1 := 'UPDATE EMRIDS SET EZEMRXID = ' ||  TO_CHAR(currentScreenImmunID) || ' WHERE PROPERTY_NAME = ''' || propertyName || '''';
          execute immediate updateQuery1;
 END;




INSERT INTO EMRGROUPMASTER VALUES (1502,'DEV',1,NULL,'GG01')


Error report:
SQL Error: ORA-04091: table CLINICALRELEASE.EMRGROUPMASTER is mutating, trigger/function may not see it
ORA-06512: at "CLINICALRELEASE.TR_INSERT_GROUPMASTER", line 16
ORA-04088: error during execution of trigger 'CLINICALRELEASE.TR_INSERT_GROUPMASTER'
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.
Re: Trigger problem [message #445039 is a reply to message #445004] Thu, 25 February 2010 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And repeating what you've already said accomplishes what exactly?
Re: Trigger problem [message #445042 is a reply to message #445039] Thu, 25 February 2010 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not the same error. Wink
But it also contains the same errors.

Regards
Michel

[Updated on: Thu, 25 February 2010 06:21]

Report message to a moderator

Re: Trigger problem [message #445045 is a reply to message #445004] Thu, 25 February 2010 07:21 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Good point.

This:
 SELECT GROUP_ID INTO v_group_id FROM EMRGROUPMASTER;


Should be:
v_group_id := :new.group_id


Or you could just not use v_group_id and reference :new.group_id directly.
Previous Topic: Monitoring the status of a pl/sql procedure (merged 7)
Next Topic: Date search in sql
Goto Forum:
  


Current Time: Mon Sep 26 11:10:41 CDT 2016

Total time taken to generate the page: 0.09590 seconds