The use of triggers in Version 2.2, ALMIS Database

The information stored in the ALMIS Database contains many 
different classification systems for occupations (DOT, SOC, 
OES, ONET, etc), industries (NAICS and SIC) and training 
programs (various releases of CIP). The states may choose 
which classification system they are going to use.  Some 
states may choose to classify occupations with OES codes 
while other states may use SOC codes.  Some states store 
data making use of several different classifications and 
some states are in the process of converting from one 
classification system to another thus needing to store data 
for both classifications. 

Since its inception, the ALMIS database has provided a 
means for specifying more than one type of classification 
system in some of the data tables. For example, the wage 
table has an occcode field in which several different types 
of occupational codes (DOT, OES, SOC, etc) can be used. The 
wage table’s occodetype field defines the type of code 
being used in a particular record. These multi-code fields 
provide flexibility in classifying the data, but can cause 
problems with data integrity.  

All relational database systems require Foreign Keys to be 
a set of fixed conditions, with the data field having the 
same length as the Primary Key of the lookup table it 
references.  Each Foreign Key constraint must reference 
only one lookup table.  This restriction presents a problem 
when trying to set Foreign Key constraints on the multi-
code fields in some of the ALMIS tables.  Since the length 
of the classification codes can vary (4 – 10 characters) 
and the lookup table to be referenced must be determined by 
the code-type, Foreign Key constraints cannot be set 
directly between the multi-code field and the lookup table 
that needs to be referenced.

In order to provide a means of enforcing Foreign Key 
constraints on the multi-code fields, two administrative 
tables were added to the ALMIS database.  They are the 
indcodes and occcodes tables.  The indcodes table is 
designed to contain the code-type and code of all the 
industry classifications a state may be using.  The 
occcodes table is designed to contain all of the occupation 
and training classifications a state may be using.  Each of 
these tables also contains the title associated with the 
classification code so these multi-code tables can also be 
used for application development.

Maintenance of the multi-code tables – indcodes and 
occcodes – can present additional effort and responsibility 
for the ALMIS Database Administrators.  In order to 
minimize the efforts of the DBA, triggers have been 
identified as a means to maintain the multi-code tables 
that can be used universally.  A trigger is a special kind 
of stored procedure that goes into effect when you modify 
data in a specified table using one of the data 
modification operations – UPDATE, INSERT, or DELETE.  
Triggers can query other tables and can include complex SQL 
statements.  Triggers in the ALMIS Database are used 
specifically to maintain the referential integrity of the 
multi-code tables with any changes that are made to the 
lookup tables.  The advantage of using triggers is they are 
automatic on all logged operations.  They are activated 
immediately after any modification to the table’s data.  
Triggers are supported by most enterprise level database 
systems.

Triggers should be added to each of the lookup tables that 
need to be represented in the multi-code tables.  For 
example, the oescode table will have a trigger that 
executes whenever it is updated and it will automatically 
update the occcodes table with the code and title changes 
made to the oescode table.  Likewise, the naiccode and 
siccode tables will have triggers that will update the 
indcodes table with the code and title changes made to the 
naiccode and siccode tables. 

A third administrative table – statelst – was added to 
support the trigger functionality.  It will allow any state 
to use the sample triggers as-is without the need to 
customize for the stfips code.  This functionality also 
applies to states that may store more than one states data 
in their database.