*************************************************************************************
* Description: Creating trigger and sequence to add primary key on an existing table
* Date: 05:13 PM EST, 08/01/2017
*************************************************************************************


<1> Synmton:
     |
     |__ o. In Oracle database, if a tables got created without primary key, the application may throw out below Error 6013:
            
                 The table/view 'EMERALIT_DEV.RENEWAL_INDUSTRY_CATEGORY' does not have a primary key defined and no valid primary key could be inferred. 
                 The table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.
             		 
							 
		
<2> Creating a preseudo primary key column on an exsiting table:		
     |
     |
     |__ o. Table DDL before applying the changes:
     |
     |           SQL> CREATE TABLE "EMERALIT"."RENEWAL_COEFFICIENTS"
     |                 (    "VAR_DESCRIPTION" VARCHAR2(50),
     |                      "VAR_COEFFICIENT" NUMBER,
     |                      "MODEL_NO" NUMBER,
     |                      "VAR_CODE" VARCHAR2(2)
     |                 ) SEGMENT CREATION IMMEDIATE
     |                PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     |               NOCOMPRESS LOGGING
     |                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     |                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     |                BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
     |                TABLESPACE "LAPPS_DATA";	 
     |		   
     |
     |__ o. Adding a new column as primary key:		   
     |       
     |           SQL> ALTER TABLE EMERALIT.RENEWAL_COEFFICIENTS ADD( RENEWAL_ID number ); 	 
     |
     |
     |__ o. Creating a sequence for the primary key value:
     |    
     |           SQL> CREATE SEQUENCE EMERALIT.RENEWAL_ID_SEQ START WITH 1 INCREMENT BY 1;		  
     |
     |	 
     |__ o. Update table with assigning sequence number as primary key value:	 
     |
     |           SQL> UPDATE EMERALIT.RENEWAL_COEFFICIENTS SET RENEWAL_ID = EMERALIT.RENEWAL_ID_SEQ.nextval; 
     |
     |
     |__ o. Creating constraint on primary key column:
     |
     |           SQL> ALTER TABLE EMERALIT.RENEWAL_COEFFICIENTS ADD CONSTRAINT PK_RENEWAL_ID PRIMARY KEY( RENEWAL_ID );
     |
     |
     |__ o. Create insert trigger to ensure primary key value got updated automatically:
	 
                 SQL> CREATE OR REPLACE TRIGGER EMERALIT.RENEWAL_ID_TRGG
                        BEFORE INSERT ON EMERALIT.RENEWAL_COEFFICIENTS
                        FOR EACH ROW
                      BEGIN
                        :new.RENEWAL_ID := EMERALIT.RENEWAL_ID_SEQ.nextval;
                      END;
                      /