SQL & PL/SQL

Hi,
I have a requirement of updating a Base Table through a View.
This View has the query using a UNPIVOT function for displaying the columns of the Base tables in rows.
I need to update/insert into/delete the Base Table by accessing the View (The user doesn't have an access to the Base Table, hence the DML's on the View).

Following is the table I've created:-

CREATE TABLE PERSON_DETAILS
(
  PID            VARCHAR2(10 BYTE),
  FIRSTNAME      VARCHAR2(1000 BYTE),
  LASTNAME       VARCHAR2(1000 BYTE),
  PHONENUMBER    VARCHAR2(1000 BYTE),
  ADDRESS1       VARCHAR2(1000 BYTE),
  ADDRESS2       VARCHAR2(1000 BYTE),
  COUNTRY_CODE   VARCHAR2(1000 BYTE),
  LANGUAGE_CODE  VARCHAR2(1000 BYTE),
  EMAIL          VARCHAR2(1000 BYTE)
)

The sample values are inserted in this table through the below script:-

insert into person_details values ('1','XYZ','ABC','1234567890','India','Asia','IN','EN','xyz@xyz.com');
insert into person_details values ('2','XYZ2','ABC2','1234567890','India','Asia','IN','EN','xyz2@xyz.com');

The code for the view is as below:-

CREATE OR REPLACE FORCE VIEW PERSON_DETAILS_VIEW
(
   PID,
   CD_NAME,
   CD_VALUE
)
AS
   SELECT "PID", "CD_NAME", "CD_VALUE"
     FROM person_details UNPIVOT INCLUDE NULLS (cd_value
                         FOR cd_name
                         IN  (firstname AS 'First Name',
                             lastname AS 'Last Name',
                             phonenumber AS 'Phonenumber',
                             address1 AS 'address1',
                             address2 AS 'address2',
                             country_code AS 'Country Code',
                             language_code AS 'Language Code',
                             email AS 'Email') );

Below are the values from the view:-

PID CD_NAME         CD_VALUE
1    First Name       XYZ
1    Last Name       ABC
1    Phonenumber  1234567890 
1    address1         India
1    address2         Asia
1    Country Code   IN
1    Language Code EN
1    Email               xyz@xyz.com
2    First Name       XYZ2
2    Last Name       ABC2
2    Phonenumber  1234567890
2    address1         India
2    address2         Asia  
2    Country Code   IN
2    Language Code EN
2    Email               xyz2@xyz.com

The user would fire some statement like below:-

update person_details_view 
set cd_value = 'US' where CD_NAME = 'IN'

The above statement should update the base table PERSON_DETAILS.

I understand I can write an INSTEAD OF trigger but I do not know what logic to write in the trigger so that the requirement gets fulfilled.

My Oracle Version

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0    Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Any help would be highly appreciated.

Thank You,
Ankit Khare.

Edited by: Ankit_Khare84 on Jun 28, 2012 2:47 PM