 |
|
APEX and table
column default values
Oracle Tips by Burleson Consulting |
Using default values for lookup table
columns
Many DBA's will design databases with built-in
audit trials, and lookup tables are no exception. In APEX
lookup tables appears as "List of Values" (LOV) in a drop-down
column.
Internally, we must design the tables and the
end user interface (EUI) with these features:
-
We must store a complete historical
record of row changes - Assume that we adopt a "current
table ==> history table" strategy where we enhance the SQL
update command with a pre-process. Before we change a row,
we use a "before update" trigger to archive all "old" row
values (similar to the BEFORE (BFOR) row image). We write
the old row values to a corresponding history table, where the
columns are identical, and the only difference is the suffix on
the table_name of "_history" (and use the 10h rowscn
pseudocolumn to keep the archive time).
-
The audits must be unobtrusive to the
end-user - All values for dates and people (last_update_by,
created_by) will be supplied by Oracle, removing all coding from
the APEX application.
For example, here we see a listing of a "Judge
Name " lookup table. Note that there is only one data column
"Magistrate Name", and everything else is audit trail supplements:
So, what Oracle table defaults can we use to
simplify the application DML and ensure that auditing is done at the
Oracle database level?
Oracle DDL Triggers for auditing
Here we create an Oracle DML (table-level)
trigger to set all of the "created_by", "updated_by", date_created,
and last_updated columns:
CREATE OR
REPLACE TRIGGER "SCHEMA_07"."AIRLINE_DML"
BEFORE INSERT OR UPDATE ON SCHEMA_07"."AIRLINE_NAMES"
FOR EACH ROW
begin
if (inserting) then
begin
:new.created_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
:new.created_date := sysdate;
:new.last_update_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
:new.last_update_date := sysdate;
if (:old.effective_date is null) then
:new.effective_date := sysdate;
end if;
if (:old.expiry_date is null) then
:new.expiry_date := to_date('01-Jan-4000','DD-Mon-YYYY');
end if;
end;
elsif (updating) then
begin
:new.last_update_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
:new.last_update_date := sysdate;
end;
end if;
end ;
The delete portion of the htmldb code must set the update columns
and the expiry column.
:new.last_update_by :=
HTMLDB_CUSTOM_AUTH.GET_USERNAME;
:new.last_update_date := sysdate;
if (expiry_date = to_date('01-Jan-4000','DD-Mon-YYYY') ) then
:new.expiry_date := sysdate;
endif;
This sets the delete date to sysdate unless the user sets a date for
expiring the row.
Enforced application-level DML
This approach to table default values has the
nice side effect that you cannot update from outside of htmldb
because HTMLDB_CUSTOM_AUTH.GET_USERNAME returns a null, which is not
allowed. To insert/update from OEM or a direct logon requires
that the trigger by
disabled first.
Connected.
SQL> UPDATE "SCHEMA_07"."AIRLINE_NAMES"
2 SET "AIRLINE_NAME" = 'Garmany Airways Update2'
3 WHERE rowid = '*BARBA2oWR2FybWFueSBBaXJ3YXlzIFVwZGF0Zf4';
UPDATE "SCHEMA_07"."AIRLINE_NAMES"
*
ERROR at line 1:
ORA-01407: cannot update
("SCHEMA_07"."AIRLINE_NAMES"."LAST_UPDATE_BY") to NULL
APEX support:
 |
For APEX development support just call to gat an
Oracle Certified professional for all APEX development
projects. |
APEX book and code samples:
|
and include the URL for the page.