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.