Adding data in a bridge table automatically

Question

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?

Answer 1

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!