?
Solved

Selecting From Triggering Table and Mutating Error - PL/SQL

Posted on 2009-12-30
13
Medium Priority
?
986 Views
Last Modified: 2013-12-07
t.tCompany is a field that carries a company name.

Since i'm running a trigger on Customer table, i can not use customer table in select. On the other hand, the only table associated with the ThirdParty table is the Customer's table. Therefore, i have to use Customer table in the Select portion and Join it with ThirdParty so i can fill t.tCompany field.

tCompany field resides in ThirdParty table. ThirdPartyID resides as FK in Customer's table. What should i do to join Customer's table and ThirdParty table without getting Mutating Error?

I hope my request is clear enough. If you need some clarification, please feel free to inform me.
CREATE OR REPLACE
  TRIGGER cushistory_aft_upddel
    AFTER UPDATE OR DELETE
    ON CUSTOMER
    FOR EACH ROW
    DECLARE
        v_action VARCHAR2(50);
    BEGIN
        v_action := CASE WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END;
        INSERT
          INTO CUSTOMERHISTORY
          SELECT  :OLD.customerID,
		  :OLD.firstname,
		  :OLD.lastname,
		  :OLD.email,
		  :OLD.thirdpartyid,
		  :OLD.address1,
		  :OLD.address2,
	          :OLD.CITY,
	          :OLD.PROVINCE,
		  :OLD.postalcode,
	          :OLD.COUNTRY,
	          :OLD.customertype,
		  :OLD.paymentmethod,
		  :OLD.numoffamilymembers,
                  .
                  .
                  t.tCompany
                  SYSDATE
            FROM  CUSTOMERFAMILYMEMBER cf
                  JOIN PHONE p ON cf.customerID = :OLD.customerID
                  JOIN BOOKEDROOM b ON b.CustomerID = :OLD.customerID
                  JOIN MOTELROOM mr ON b.roomID = mr.roomID
                  JOIN MOTEL m ON mr.motelID = m.motelID
            WHERE cf.customerID = :OLD.customerID;
END;
/

Open in new window

0
Comment
Question by:F-J-K
  • 8
  • 5
13 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 26151306
Hi F-J-K,

why not try doing
(SELECT t.tCompany FROM ThirdParty t WHERE t.ThirdPartyID = :OLD.ThirdPartyID)

lwadwell
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151347
Where should i put this? In place of the t.tCompany attribute?
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 26151382
F-J-K,

yes ... you could use that where you currently have t.tCompany in the INSERT ... SELECT ...

make sure to have the brackets around this inner select statement.

lwadwell
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:F-J-K
ID: 26151553
The error i used to get before your response is: PL/SQL: ORA-00947: not enough values

After i did you have suggested, i get an error that says too many values. PL/SQL: ORA-00913: too many values.

I posted my fixed Trigger below

----------------------------

Just to inform you, CustomerHistoryID primary key is filled automatically by the trigger:

CREATE OR REPLACE TRIGGER cushistory_bef_insert
BEFORE INSERT ON CustomerHistory
FOR EACH ROW
BEGIN
       SELECT customerhistory_id_seq.NEXTVAL INTO :NEW.CustomerHistoryID
       FROM DUAL;
END;
/

CREATE OR REPLACE
  TRIGGER cushistory_aft_upddel
    AFTER UPDATE OR DELETE
    ON CUSTOMER
    FOR EACH ROW
    DECLARE
        v_action VARCHAR2(50);
    BEGIN
        v_action := CASE WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END;
        INSERT
          INTO CUSTOMERHISTORY
          SELECT  
		  :OLD.customerID,
		  :OLD.firstname,
		  :OLD.lastname,
		  :OLD.email,
		  :OLD.address1,
		  :OLD.address2,
	          :OLD.CITY,
	          :OLD.PROVINCE,
		  :OLD.postalcode,
	          :OLD.COUNTRY,
	          :OLD.customertype,
		  :OLD.paymentmethod,
		  		  :OLD.thirdpartyid,
		  :OLD.numoffamilymembers,
		  cf.fFirstname,
		  cf.fLastname,
	          cf.fAge,
		  cf.fSibling,
		  p.phonetype,
		  p.phonenumber,
		  (SELECT t.company, t.firstname, t.lastname, t.email 
		   FROM THIRDPARTY t 
		   WHERE t.ThirdPartyID = :OLD.ThirdPartyID),
		  m.branchname,
		  mr.ROOMTYPE,
		  b.checkin,
		  b.checkout,
		   SYSDATE,
                  v_action
            FROM  CUSTOMERFAMILYMEMBER cf
                  JOIN PHONE p ON cf.customerID = :OLD.customerID
                  JOIN BOOKEDROOM b ON b.CustomerID = :OLD.customerID
                  JOIN MOTELROOM mr ON b.roomID = mr.roomID
                  JOIN MOTEL m ON mr.motelID = m.motelID
            WHERE cf.customerID = :OLD.customerID;
END;
/ 

Open in new window

0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151559
Here is my history table columns. All attributes in the above trigger follow same sequence of History table columns...

The error i get when running the trigger is: . PL/SQL: ORA-00913: too many values.

Any idea where i went wrong?

Thanks
CREATE TABLE CustomerHistory
(
	CustomerHistoryID   number(10) CONSTRAINT customerhistoryid_pk PRIMARY KEY,
	customerID          number(7),
	firstname           varchar2(50),
        lastname            varchar2(50),
        email               varchar2(50),
        address1            varchar2(100),
        address2            varchar2(100),
        city                varchar2(50),  
        province            varchar2(50),                
        postalcode          varchar2(50),
        country             varchar2(50),
        customertype        varchar2(20), 
	paymentmethod       varchar2(20),  
	thirdpartyid        number(7),
	numoffamilymembers  number(1)    DEFAULT 0,
	fFirstname  	    varchar2(50) DEFAULT 'None',
        fLastname    	    varchar2(50) DEFAULT 'None',
        fAge         	    number(3)	 DEFAULT 0,
        fSibling     	    varchar2(20) DEFAULT 'None',
	phonetype    	    char(1),             
	phonenumber         varchar2(100),
        company      	    varchar2(50) DEFAULT 'None',	
        tFirstname   	    varchar2(50) DEFAULT 'None',
        tLastname      	    varchar2(50) DEFAULT 'None',
        tEmail               varchar2(50) DEFAULT 'None',
	branchname	    varchar2(50), 
	roomtype   	    char(3),
        checkin        	    timestamp,
        checkout            timestamp,
	createddate	    timestamp	 DEFAULT sysdate,
	operation	    varchar2(50)
);

Open in new window

0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 2000 total points
ID: 26151612
F-J-K,

I would (and as a matter of habit) specifiy the columns on the INSERT, e.g.
INSERT INTO CUSTOMERHISTORY (customerID,firstname,lastname,email,address1,address2,city
,province,postalcode,country,customertype,paymentmethod
,thirdpartyid,numoffamilymembers,fFirstname,fLastname
,fAge,fSibling,phonetype,phonenumber,company,tFirstname
,tLastname,tEmail,branchname,roomtype,checkin,checkout
,createddate,operation)
          SELECT  ... etc.

Also ... in the SELECT you cannot return multiple values in a subselect as multiple columns i.e. (SELECT t.company, t.firstname, t.lastname, t.email FROM THIRDPARTY t WHERE t.ThirdPartyID = :OLD.ThirdPartyID).  You can only return the one column as one column.

If you need to have the four values you can either:
1. perform the SELECT four times ... for the different columns, or
2. BEFORE the INSERT do the SELECT once as "SELECT t.company, t.firstname, t.lastname, t.email INTO v_company, v_firstname, v_lastname, v_email FROM THIRDPARTY t WHERE t.ThirdPartyID = :OLD.ThirdPartyID" as use the variables in the INSERT.  You will need to declare the four new variables as well.


lwadwell
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 26151634
F-J-K,

or maybe this as the SELECT

lwadwell
SELECT :OLD.customerID,:OLD.firstname,:OLD.lastname,:OLD.email,
       :OLD.address1,:OLD.address2,:OLD.CITY,:OLD.PROVINCE,
       :OLD.postalcode,:OLD.COUNTRY,:OLD.customertype,
       :OLD.paymentmethod,:OLD.thirdpartyid,:OLD.numoffamilymembers,
       cf.fFirstname,cf.fLastname,cf.fAge,cf.fSibling,p.phonetype,
       p.phonenumber,v.company, v.firstname, v.lastname, v.email, 
       m.branchname,mr.ROOMTYPE,b.checkin,b.checkout,SYSDATE,v_action
 FROM  CUSTOMERFAMILYMEMBER cf
       JOIN PHONE p ON p.customerID = cf.customerID
       JOIN BOOKEDROOM b ON b.CustomerID = cf.customerID
       JOIN MOTELROOM mr ON b.roomID = mr.roomID
       JOIN MOTEL m ON mr.motelID = m.motelID
       JOIN (SELECT t.company, t.firstname, t.lastname, t.email 
             FROM THIRDPARTY t 
             WHERE t.ThirdPartyID = :OLD.ThirdPartyID) v ON v.customerID = cf.customerID
WHERE cf.customerID = :OLD.customerID;

Open in new window

0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 2000 total points
ID: 26151641
F-J-K,

woops ... forgot to add the dummy column into the inline view to make the join work.

lwadwell
SELECT :OLD.customerID,:OLD.firstname,:OLD.lastname,:OLD.email,
       :OLD.address1,:OLD.address2,:OLD.CITY,:OLD.PROVINCE,
       :OLD.postalcode,:OLD.COUNTRY,:OLD.customertype,
       :OLD.paymentmethod,:OLD.thirdpartyid,:OLD.numoffamilymembers,
       cf.fFirstname,cf.fLastname,cf.fAge,cf.fSibling,p.phonetype,
       p.phonenumber,v.company, v.firstname, v.lastname, v.email, 
       m.branchname,mr.ROOMTYPE,b.checkin,b.checkout,SYSDATE,v_action
 FROM  CUSTOMERFAMILYMEMBER cf
       JOIN PHONE p ON p.customerID = cf.customerID
       JOIN BOOKEDROOM b ON b.CustomerID = cf.customerID
       JOIN MOTELROOM mr ON b.roomID = mr.roomID
       JOIN MOTEL m ON mr.motelID = m.motelID
       JOIN (SELECT :OLD.customerID as customerID, t.company, t.firstname, t.lastname, t.email 
             FROM THIRDPARTY t 
             WHERE t.ThirdPartyID = :OLD.ThirdPartyID) v ON v.customerID = cf.customerID
WHERE cf.customerID = :OLD.customerID;

Open in new window

0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151678
I tried all your suggestions carefully. I keep getting this error: PL/SQL: ORA-00947: not enough values ... Odd. Anyway, thanks for help. I will try to figure it out...
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151682
I just got your last two responses. I will check them out. I will be back
0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151714
For some odd reason, i still get same error . I think all suggestions you gave me are correct, but the error occurs due to some tiny nonsense mistake. If i figure it out i will let you know.

The error lies in here PL/SQL: ORA-00947: not enough values - line 6, column 16:

   DECLARE
CREATE OR REPLACE
  TRIGGER cushistory_aft_upddel
    AFTER UPDATE OR DELETE
    ON CUSTOMER
    FOR EACH ROW
    DECLARE
        v_action VARCHAR2(100);
    BEGIN
        v_action := CASE WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END;
        INSERT
          INTO CUSTOMERHISTORY(CustomerHistoryID,
	customerID,
	firstname,
        lastname,
        email,
        address1,
        address2,
        CITY,  
        PROVINCE,                
        postalcode,
        COUNTRY,
        customertype, 
	paymentmethod,  
	thirdpartyid,
	numoffamilymembers,
	fFirstname,
        fLastname,
        fAge,
        fSibling,
	phonetype,             
	phonenumber,
        company,	
        tFirstname,
        tLastname,
        tEmail,
	branchname, 
	ROOMTYPE,
        checkin,
        checkout,
	createddate,
	operation)
          SELECT  
		  :OLD.customerID,
		  :OLD.firstname,
		  :OLD.lastname,
		  :OLD.email,
		  :OLD.address1,
		  :OLD.address2,
	          :OLD.CITY,
	          :OLD.PROVINCE,
		  :OLD.postalcode,
	          :OLD.COUNTRY,
	          :OLD.customertype,
		  :OLD.paymentmethod,
		  		  :OLD.thirdpartyid,
		  :OLD.numoffamilymembers,
		  cf.fFirstname,
		  cf.fLastname,
	          cf.fAge,
		  cf.fSibling,
		  p.phonetype,
		  p.phonenumber,
		  v.company, 
		  v.firstname, 
		  v.lastname, 
		  v.email,
		  m.branchname,
		  mr.ROOMTYPE,
		  b.checkin,
		  b.checkout,
		   SYSDATE,
                  v_action
            FROM  CUSTOMERFAMILYMEMBER cf
                  JOIN PHONE p ON cf.customerID = :OLD.customerID
                  JOIN BOOKEDROOM b ON b.CustomerID = :OLD.customerID
                  JOIN MOTELROOM mr ON b.roomID = mr.roomID
                  JOIN MOTEL m ON mr.motelID = m.motelID
				  JOIN (SELECT :OLD.customerID AS customerID, t.company, t.firstname, t.lastname, t.email 
             	  	   FROM THIRDPARTY t WHERE t.ThirdPartyID = :OLD.ThirdPartyID) v ON v.customerID = cf.customerID
            WHERE cf.customerID = :OLD.customerID;
END;
/ 

Open in new window

0
 
LVL 1

Author Comment

by:F-J-K
ID: 26151723
Great! Solved .... INTO CUSTOMERHISTORY(CustomerHistoryID, .....

CustomerHistoryID was causing the trouble...

0
 
LVL 1

Author Closing Comment

by:F-J-K
ID: 31671480
One of the best supports i have ever had in Expert Exchange...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

621 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