0

Basically the concept is that a customer can place orders from multiple addresses and an address can have multiple customers, like different members of the same family.

CREATE TABLE Customer (
  cust_id    NUMBER PRIMARY KEY,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE Address (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE Customer_Address (
  cust_id    NUMBER REFERENCES Customer ( cust_id ),
  Address_id NUMBER REFERENCES Address ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

Now I want to automate the process of entry of ids into the Customer_Address table i.e. When a record is added into the customer and address table, the ids are automatically added into the customer_address table.

I have tried this by creating a view where I inserted the ids of customer and address tables and then using the instead of insert trigger to populate the junction table but it did not work.

I created the view and trigger:

CREATE VW_CUSTADD AS 
SELECT CUST_ID, ADDRESS_ID FROM CUSTOMER, ADDRESS;

CREATE OR REPLACE TRIGGER TG_CustAdd
INSTEAD OF INSERT ON VW_CustAdd
FOR EACH ROW
ENABLE
BEGIN
INSERT INTO CUSTOMER_ADDRESS VALUES (:NEW.CUST_ID,:NEW.ADDRESS_ID);
END;

The data is getting inserted into the view but not the junction table (Customer_Address). Have I made any mistake?

2
  • 2
    How are you adding addresses to the system? If you are using a stored procedure to add new addresses, just have it accept the relevant customer ID and then you can modify it to simultaneously update the cust_add table. Apr 26, 2017 at 14:22
  • The first thing added is the phone no. of the customer. Then we check if the phone no. already exists in the customer table we use the id corresponding to that phone no. else we create a new id and add customer details and address details. Apr 27, 2017 at 12:21

1 Answer 1

0

When contemplating the problem that your are describing, I thought that a VIEW and a TRIGGER won't give you the solution you are after. (My current view is: a VIEW should be used for viewing or for creating "virtual tables", and TRIGGERs are best avoided ... but that's just me.) Instead, I have written some example code for a PROCEDURE that could do the job. I have changed the table names slightly (I have used the prefix "se" for "stackexchange"), but have kept the column names and the constraints:

-- DDL code
CREATE TABLE SECustomer (
  cust_id    number primary key,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE SEAddress (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE SECustAdd (
  cust_id    NUMBER REFERENCES SECustomer ( cust_id ),
  Address_id NUMBER REFERENCES SEAddress ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

For the procedure's logic, I have more or less coded what you have specified in your comment (27 Apr 2017, 1221 hours):

"The first thing added is the phone no. of the customer. Then we check if the phone no. already exists in the customer table we use the id corresponding to that phone no. else we create a new id and add customer details and address details."

I have sprinkled some comments into the code, so that it is easier to see what's going on. Also, there are quite a few dbms_output.put_line() statements. These should be removed if you really use the procedure in a "serious" setting.

create or replace procedure addnewclient (
  phone VARCHAR2 
, customername VARCHAR2
, anaddress VARCHAR2 
)
as
  phonecount number := 0 ; 
  newcustid number := 0 ;    -- only needed if the customerid is NOT generated
  maxcustid number := 0 ;    -- latest/highest customer_id using a particular phone number
  newaddressid number := 0 ; -- only if addressid is NOT generated
  existingaddr number := 0 ;
  errorcode number := 0 ;
  errormsg varchar2(128) := '' ;
begin
  -- does the phone number exist?
  select count(cust_phone) into phonecount from secustomer
  where phone = cust_phone ;

  -- if ids are NOT "generated always as identity"
  select max(cust_id) + 1 into newcustid from secustomer ;

  if phonecount > 0 then        -- phone number exists, use an existing address
     dbms_output.put_line('phone number ' || phone || ' EXISTS in customer table') ;
     -- Use the newest customerid (in customer table)
     select max(cust_id) into maxcustid from secustomer where cust_phone = phone ;
     insert into secustomer (cust_id, cust_phone) values (newcustid, phone);
     dbms_output.put_line('  INSERT (Customer) -> new customer id, existing phone number ') ;

     -- Find the address id for the newest customerid (in intersection)
     select address_id into existingaddr from secustadd where cust_id = maxcustid ;
     insert into secustadd values (newcustid, existingaddr) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, existing address id') ;     

  elsif phonecount = 0 then     -- we need to record a new address
     dbms_output.put_line('phone number ' || phone || ' does NOT exist in customer table') ; 
     -- Generate a new id (only needed if customer_id is NOT generated!) 
     select max(address_id) + 1 into newaddressid from seaddress ;

     insert into secustomer (cust_id, cust_phone) values (newcustid, phone) ;
     dbms_output.put_line('  INSERT (Customer) -> new customer id, new phone number ') ;     
     insert into seaddress (address_id, address) values (newaddressid, anaddress) ;
     dbms_output.put_line('  INSERT (Address) -> new address id, a new address ') ;    
     insert into secustadd values (newcustid, newaddressid) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, new address id ') ;     

  end if;
exception
  when others then
    errorcode := SQLCODE;
    errormsg := SUBSTR(SQLERRM, 1, 128);
    dbms_output.put_line('ERROR: ' || errorcode || '  ' || errormsg ) ;
end;

I have assumed that you are NOT using auto-ids (...generated always as identity). For testing, I suggest that you INSERT just one line of test data into each table - for starters -, and then call the procedure from an anonymous block (several times).

-- insert some test data
begin
  insert into secustomer (cust_id, cust_phone)  values (1, '123456789') ;
  insert into seaddress (address_id) values (50) ;
  insert into secustadd values (1, 50) ;
end;

-- testing:

begin
  -- phone number exists
  addnewclient('123456789', 'first_new', 'newaddress1') ;
end;

begin
  -- phone number does not exist
  addnewclient('333444555', 'second_new', 'newaddress2') ;
end;

I'm sure this will need some more tweaks (exception handling etc), but it should get you started. Best of luck!

7
  • This is really a nice one. It surely did the job for me. Thanks for your help. Actually my ids are generated as identity but that is something I can take care of. Thanks a lot. May 3, 2017 at 2:58
  • Thanks for your comment! I have used Oracle 12c, by the way.
    – stefan
    May 3, 2017 at 8:03
  • Oh ok i generated ids from sequence anyway May 3, 2017 at 10:47
  • You are aware of the fact that sequences generate unique numbers, but they are NOT free of gaps? (see the WARNING box in the documentation ... docs.oracle.com/database/121/CNCPT/schemaob.htm#CNCPT88878 ) I don't think it's will post a problem in your case, as long as you know.
    – stefan
    May 3, 2017 at 10:56
  • Yeah it's doesn't really matter with customers but with order table it does. If not sequence then how to achieve this ? Sequential values without gap generated automatically? May 3, 2017 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.