… well, at least than I thought. Specifying default values for columns in our database tables seems like a pretty simple and straightforward task. The default is either a static value or SYSDATE or USER, this value is applied in the absence of a value in the insert statement and that;s about the extent of it. More complex defaults can be derived in before insert row-level triggers. Good luck.
Well, not entirely. Default values can be more than statics or those two pseudo-columns. And using Before Insert row-level triggers is not a straightforward as you might think. For example: think about the case where you have multiple before insert row level triggers while – as you know – you cannot control or even know the order in which the triggers fire.
In this article I will describe a couple of new insights into the world of default values that hit me over the past few days. The resource listed at the end of this article did a lot to get me started down this track.
The KANE system – for recording new born citizens
For my local city council, I have created a virtual system for the registration of new born babes.
create table citizens ( name varchar2(100) , birthdate date , city varchar2(50) ) /
This table is at the heart of the application used by the local municipality to register new born citizens as declared by the fathers. In this simplified example, we only record the name, birthdate and city for each newborn.
It turns out that the birthdate is in the vast majority of cases the day prior to the date of entry for each record (sysdate minus one in Oracle SQL terms). The City is currently always Zoetermeer, as this is the only city using our KANE system. To help the users and allow them to only provide values for those cases that deviate from the standard, we will make use of column default values, like this:
create table citizens ( name varchar2(100) , birthdate date default sysdate - 1 , city varchar2(50) default 'Zoetermeer' ) /
With this definition of our table, the user do not need to provide values for the birthdate and city columns when they create new citizen records.
That means that a statement like:
insert into citizens ( name) values ( 'Tobias Jellema') / NAME --------------------- BIRTHDATE CITY --------- ----------- Tobias Jellema 04-APR-06 Zoetermeer
Creates a record as desired.
Note that the following statement does not doe what you might expect – depending on your interpretation of what NULL means:
insert into citizens ( name, birthdate, city) values ( 'Lex Jellema', null, null) / NAME ------------------------ BIRTHDATE CITY --------- -------------- Lex Jellema
Default values are only applied by Oracle for a column for which no value is provided in the insert statement. Even though NULL is the blatant absence of a value, explicitly specifying NULL for a column is interpreted as forcing the column to be empty for this particular record. No default value is applied to a column that is included in the insert statement.
Is the default already applied when the before insert row level trigger fires?
We frequently make use of triggers – especially before row insert triggers – to perform validation of the provided values and calculate values for derived columns. I was not quite sure, but when we do not provide a value for a column, and the default value is applied at insert time, the before row insert trigger will see the value that the record eventually will have in the database:
create or replace trigger citizen_bri
before insert on citizens
for each row
begin
dbms_output.put_line('The city for the new record: '||:new.city);
end;
/
insert into citizens
( name)
values
( 'Wessel Verloop')
/
The city for the new record: Zoetermeer
1 row created.
The trigger does not know whether the value is provided by the user in the insert statement or by the database in case of omission of a vaue (the default).
The DEFAULT keyword in Insert and Update statements
Some tools and applications tend to provide values for all columns in a table, thereby obviating the use of the default values. We can however include all columns in our insert statement and still have the default values applied. We do this through the keyword default, used instead of a value expression in the insert statement. For example:
insert into citizens ( name, birthdate, city) values ( 'Jules Pronk', default, default) / NAME ------------------------ BIRTHDATE CITY --------- -------------- Jules Pronk 04-APR-06 Zoetermeer
If we had specified a default value for column name, say
alter table citizens modify (name varchar2(50) default 'John Doe' ) / insert into citizens (name) values (default) / NAME BIRTHDATE -------------------------------------------------- --------- CITY -------------------------------------------------- John Doe 04-APR-06 Zoetermeer If you don't explicitly declare a DEFAULT value, Oracle implicitly defines the default value to be NULL, and the DEFAULT keyword will even work with such.
Note: the keyword DEFAULT can also be used in update statements:
update citizens set name = default / 5 rows updated. select name from citizens / NAME ------------------------------- John Doe John Doe John Doe John Doe John Doe rollback /
More complex expressions for Column Defaults
We have seen how a default value can be defined using SQL expressions like SYSDATE -1 in addition to simple static values. We can explore the options a little here. Suppose for example that in general BIRTHDATE should be calculated as SYSDATE – 1, except for records created on mondays, where typically enough most birthdates are actually from the previous friday. We could now specify the default value as follows:
alter table citizens
modify (birthdate date default case substr(to_char( sysdate, 'DAY' ),1,1)
when 'M'
then sysdate - 3
else sysdate -1
end
)
/
insert into citizens (name)
values ('Ole Vink')
/
NAME BIRTHDATE
-------------------------------------------------- ---------
CITY
--------------------------------------------------
Ole Vink 04-APR-06
Zoetermeer
Since today is not Monday, I cannot really test whether the default is derived as I think it should be. Today is Wednesday, so let’s change the default expression for testing purposes:
alter table citizens
modify (birthdate date default case substr(to_char( sysdate, 'DAY' ),1,1)
when 'W'
then sysdate - 3
else sysdate -1
end
)
/
insert into citizens (name)
values ('Jasper Gevers')
/
NAME
BIRTHDATE
-------------------------------------------------- ---------
CITY
--------------------------------------------------
Jasper Gevers 02-APR-06
Zoetermeer
So indeed, it does.
The Marker Default value
We could implement derivations of default values using PL/SQL logic in Before Insert Record level triggers. That way, we can implement much more advanced business rules for calculating default values. However, we only should apply default values is no values were supplied in the Insert statement. Unfortunately, we cannot really determine whether a value was provided – and we should not set a complex default – or no value was provided and we SHOULD derive our complex default.
alter table citizens
modify (birthdate date default to_date('01-01-1900','dd-mm-yyyy')
)
/
create or replace
function special_birthdate_derivation
return date
is
begin
return sysdate - 1;
end special_birthdate_derivation;
/
create or replace trigger citizen_default_birthdate_bri
before insert on citizens
for each row
declare
c_marker_date date := to_date('01-01-1900','dd-mm-yyyy');
begin
if :new.birthdate = c_marker_date
then
-- the value for birthdate apparently is the markervalue - which means that the user did not provide a value and the database applied the default value
-- our complex default derivation logic should kick in to replace the marker-default with the calculated default. This aspproach allows us to use very complex, user defined PL/SQL.
if substr(to_char(sysdate ,'DAY'),1,1) = 'M'
then
:new.birthdate:= sysdate - 3;
else
-- now call some very intricate custom PL/SQL
:new.birthdate:= special_birthdate_derivation;
end if;
end if;
end;
/
By checking for the Marker Default, we know when we should apply our own real default value – when the new value of a column is equal to the marker default, we know the user did not provide a value and therefore we should. There is a limitation to this approach: we can have multiple before insert row-level triggers on our table. One of these would be the one checking for the marker default and applying a real value, others can do validation of row-level business rules, derivation of denormalized columns or columns whose initial value depends on other columns (like set end date initially equal to two weeks after begin date) etc.
However, we cannot control – or even know – the order in which these triggers are fired! Hence, our default deriving trigger may fire well after the triggers that perform validation and/or derivation, in which case they will work with the marker default, which is not the real value! The next section introduces the use of application context with default values; this may help us overcome this last limitation.
Using Application Context to derive default value
One additional interesting option with specifying the default expression: we can use a call to sys_context to extract a value from an application contact. Obvious examples are the many values we can extract from the USERENV context, like DB_DOMAIN, HOST, IP_ADDRESS, LANGUAGE, PROTOCOL, TERMINAL. See: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#i1038176for more examples.
We can use our own application context – extract values from that context to derive the default value to apply.
We can make use of this option with the following business specification for the KANE system: our system will be used by more than one City. No longer will the default value be ‘Zoetermeer’ in all cases. The system will have users working for the Zoetermeer municipality – as we had in the past – but will also have users working for other cities. So the default value for the City column is suddenly dynanic – depending on the user performin the insert. We can look at a number of parameters in the USERENV context for identifying the user; in this case, we will pick SESSION_USER.
We create a new table, holding our KANE_USERS:
create table KANE_USERS
( username varchar2(100)
, city varchar2(100)
)
/
insert into KANE_USERS
(username, city)
values
('SCOTT', 'DEN HAAG')
/
insert into KANE_USERS
(username, city)
values
('KANE', 'DELFT')
/
Now we create the application context – namespace – that will store value related to our KANE application. We can reference this context using the sys_context function, references we may also use in the definition of default column values:
CREATE OR REPLACE CONTEXT KANE_CTX USING KANE_CTX_MGR /
Note: each context needs an associated package that is used for manipulating the values in the context. Only PL/SQL code in this associated package may call dbms_session.set_context for placing values into the context.
create or replace package KANE_CTX_MGR
is
procedure set_default_city
( p_city in varchar2
);
end;
/
create or replace package body KANE_CTX_MGR
is
procedure set_default_city
( p_city in varchar2
) is
begin
dbms_session.set_context('KANE_CTX' , 'DEFAULT_CITY', p_city);
end set_default_city;
end;
/
Now we change the default column value for the city column; instead of a hard coded value (‘Zoetermeer’), we now specify that the default is to be derived from the application context KANE_CTX:
alter table citizens
modify (city varchar2(50) default sys_context('KANE_CTX', 'DEFAULT_CITY' ))
/
And now finally the logon trigger that wll derive the user dependent City Default, to be set when the user connects to the database:
CREATE OR REPLACE TRIGGER set_kane_defaults_trg
AFTER LOGON ON SCHEMA
begin
for user in (select city from kane_users where username = sys_context('USERENV', 'SESSION_USER')) loop
kane_ctx_mgr.set_default_city( p_city => user.city);
end loop;
end;
/
Let’s see whether the mechanism we put in place actually functions. I reconnect to the database as SCOTT and:
select sys_context('KANE_CTX', 'DEFAULT_CITY')
from dual
/
SYS_CONTEXT('KANE_CTX','DEFAULT_CITY')
-------------------------------------------------------
DEN HAAG
insert into citizens (name)
values ('Laurens Bongers')
/
NAME BIRTHDATE
-------------------------------------------------- ---------
CITY
--------------------------------------------------
Laurens Bongers 05-APR-06
DEN HAAG
Reconnecting as KANE:
connect kane/kane@orcl
insert into citizens (name)
values ('Floyd Flanders')
/
NAME BIRTHDATE
-------------------------------------------------- ---------
CITY
--------------------------------------------------
Floyd Flanders 05-APR-06
DELFT
So now we have specified a default value that depends on the user who is currently connected. We could have done this using a Before Row level trigger if we had used the Marker Default to find out whether a value was or was not provided and therefore a default should not or should be applied. I think this application context based implementation is more elegant.
Changing conditions
It is very well possible that during the day one of the Kane users temporarily steps in for a colleague. SCOTT, who normally represents DEN HAAG, may ta
ke over from a co-worker who is flooded with new borns and goes home early. At that moment, clearly the default value set during logon for SCOTT – based on the city he typically presents defined in the KANE_USERS table does not suffice any longer. Now SCOTT may type in the City he temporarily represents any time he makes a new entry – by passing the default altogether. He can also change the value in the application context to the City that is currently his default:
begin kane_ctx_mgr.set_default_city( p_city => 'GOUDA'); end;
This piece of PL/SQL can be executed from the client application; we could envision a button in the GUI that says: take over from <name of colleague> or Represent City <name of City> at this moment; when pressed, the default city would be changed in the application context.
Efficiency has hit – and the birth rate has declined – so our KANE_USERS will not work for one city all day. Suppose there is a rostar defined that specifies for each working day the hours during which a user will represent a certain city. This specifies for example for user SCOTT that from 8AM until 9.30AM he works for ZOETERMEER, from 9.30AM until Noon he is a DEN HAAG representative; after lunch until 3PM, SCOTT is a DELFT man and from 3PM until 5PM his default city value is to be GOUDA. How could we implement this, without SCOTT having to push buttons or explicitly tell the system he changes roles?
Let’s extend the KANE_USERS table:
alter table kane_users
add
( start_time varchar2(5)
, end_time varchar2(5)
)
/
insert into kane_users
( username, city, start_time, end_time)
values
('SCOTT', 'NIEUWEGEIN', '05.30', '08.30')
/
insert into kane_users
( username, city, start_time, end_time)
values
('SCOTT', 'ZOETERMEREER', '08.30', '09.30')
/
insert into kane_users
( username, city, start_time, end_time)
values
('SCOTT', 'DEN HAAG', '09.30', '12.00')
/
insert into kane_users
( username, city, start_time, end_time)
values
('SCOTT', 'DELFT', '12.30', '15.00')
/
insert into kane_users
( username, city, start_time, end_time)
values
('SCOTT', 'GOUDA', '15.00', '17.00')
/
Having this information in the database is great of course, but how do we now automatically change the value set in our application context? One way could be to schedule jobs to run at 8.30AM, 9.30AM etc. to change the application context’s city value. Obivously that is not an elegant solution! What we can do is make sure, just prior to running an insert statement, that the correct value is set in the context. We have seen that the database applies default values prior to executing the before insert row level triggers. However, the before insert statement level trigger is fired before individual records are processed. So in our statement level trigger we can set the correct value in the application context and have that value used as default when microseconds later the default value is applied to the city column.
create or replace trigger citizen_set_city_default_bSi
before insert on citizens
begin
-- given the current user AND the current time, specify the city default:
for user_default in (select city
from kane_users
where username = sys_context('USERENV', 'SESSION_USER')
and to_char(sysdate, 'HH24:MI') between start_time and end_time
) loop
kane_ctx_mgr.set_default_city( p_city => user_default.city);
end loop;
end;
/
select to_char(sysdate, 'HH24:MI')
from dual
/
insert into citizens (name)
values ('Alex Timmerman')
/
select to_char(sysdate, 'HH24:MI') TIME
from dual
/
TIME
-----
06:52
NAME BIRTHDATE
-------------------------------------------------- ---------
CITY
--------------------------------------------------
Alex Timmerman 05-APR-06
NIEUWEGEIN
What have we achieved? A programmatically derived default without resorting to marker default values. The default default is set from the LOGON trigger, a conditionally determined default value is derived just prior to executing the insert operation at record level.
Resource
Oracle Tip: How to use default values with database columns by Scott Stephens
