2.1.2 Common Columns
In PPDM v3.8, every table has 11 common or standard columns.  These columns allow for the consistent implementation of business rules.  These rules can be listed in the table below.
Rule #
Business Rule
Column Name
1
identify if the row is still active
ACTIVE_IND
2
identify the date that the row became active in your organization
EFFECTIVE_DATE
3
identify the date when the row is no longer considered to be active
EXPIRY_DATE
4
uniquely identify the row
PPDM_GUID
5
capture a comment or remark about the row 
REMARK
6
identify the source of the row
SOURCE
7
identify who last changed a row
ROW_CHANGED_BY
8
identify when the row was last changed
ROW_CHANGED_DATE
9
identify who created a row
ROW_CREATED_BY
10
identify when a row was created
ROW_CREATED_DATE
11
identify the confidence of a row
ROW_QUALITY
By populating these columns, a variety of questions about the data can asked.  These questions include:
  • How many records in the WELL were added in the last year, group it by the month?
  • How many records were loaded by the Seismic group in the last two weeks?
  • What records have become inactive in the last two years?
It should be noted that these types of questions can be asked of any table as long as the data is populated.  And how better to populate this data than to use a trigger on each table to automatically populate the columns listed in the table below.
Rule #
Column Name
Trigger Value & Explanation
1
ACTIVE_IND
Set to ‘Y’
2
EFFECTIVE_DATE
set to the truncated sysdate (Oracle function that returns the present date/time)
3
EXPIRY_DATE
 
 
4
PPDM_GUID
set to a value from sys_guid (Oracle function that  returns a unique 38 character value)
7
ROW_CHANGED_BY
set to a combination of the Windows (or Unix) login and the hardware terminal id
8
ROW_CHANGED_DATE
set to the sysdate
9
ROW_CREATED_BY
set to a combination of the Windows (or Unix) login and the hardware terminal id
10
ROW_CREATED_DATE
set to the sysdate
11
ROW_QUALITY
set to 'NOT CLASSIFIED'
These values are added if nothing is being passed in the DML statement.  If there is a value, then the value passed in, then that value has preference. Every table in PPDM should have this trigger implemented to provide consistent background data management information to be capture.  Using the AREA table as an example, the trigger would look like this:
CREATE OR REPLACE TRIGGER AREA_IU
BEFORE INSERT OR UPDATE ON AREA 
FOR EACH ROW
DECLARE
  v_user AREA.ROW_CREATED_BY%TYPE;
BEGIN
  --
  -- Generated on Nov-2008
  --
  SELECT  SUBSTR(UPPER(sys_context('USERENV','os_user'))||’- '||sys_context('USERENV','terminal'),1,30) INTO  v_user FROM dual;
  IF INSERTING THEN
    IF :NEW.active_ind IS NULL THEN
      :NEW.ACTIVE_IND := 'Y';
    END IF;
    IF :NEW.effective_date IS NULL THEN
      :NEW.EFFECTIVE_DATE := TRUNC(SYSDATE);
    END IF;
    IF :NEW.ppdm_guid IS NULL THEN
      :NEW.PPDM_GUID := sys_guid();
    END IF;
    IF :NEW.row_created_by IS NULL THEN
      :NEW.ROW_CREATED_BY := v_user;
    END IF;
    IF :NEW.row_created_date IS NULL THEN
      :NEW.ROW_CREATED_DATE := SYSDATE;
    END IF;
    IF :NEW.row_changed_by IS NULL THEN
      :NEW.ROW_CHANGED_BY := v_user;
    END IF;
    IF :NEW.row_changed_date IS NULL THEN
      :NEW.ROW_CHANGED_DATE := SYSDATE;
    END IF;
    IF :NEW.row_quality IS NULL THEN
      :NEW.ROW_QUALITY := 'NOT CLASSIFIED';
    END IF;
   ELSIF UPDATING THEN
    :NEW.ROW_CHANGED_BY := v_user;
    :NEW.ROW_CHANGED_DATE := SYSDATE;
  END IF;
END;
/
This trigger is implemented as a BEFORE Insert or Delete on the AREA table. This means that it is fired before each insert or update statement that is issued at the AREA table.  The majority of the trigger fires if it is an Insert statement.   If it's an update, then only the Row_Changed_By & Row_Changed_Date fields are altered.  This allows the system to capture who the last person/process/application was to update the row.  It does not create a true audit trail but that could be implemented via the PPDM Audit tables.
By using a trigger like this, SQL developers do not have to write the redundant code to add the data in.  This does not mean that they can't or shouldn't.  It just means that if they don't, the information will still be captured and it will be captured in a consistent method.
Top