asked on

Selecting From Triggering Table and Mutating Error - PL/SQL

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

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

F-J-K

ASKER

Where should i put this? In place of the t.tCompany attribute?

Lee Wadwell

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

F-J-K

ASKER

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

F-J-K

ASKER

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

SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee Wadwell

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

SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

F-J-K

ASKER

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...

F-J-K

ASKER

I just got your last two responses. I will check them out. I will be back

F-J-K

ASKER

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

F-J-K

ASKER

Great! Solved .... INTO CUSTOMERHISTORY(CustomerHistoryID, .....

CustomerHistoryID was causing the trouble...

F-J-K

ASKER

One of the best supports i have ever had in Expert Exchange...