Quad-Based Data Validation Procedures - Program Flow

This document gives a brief description of each trigger that fires as records from agencies that place traps on a quad-based system are loaded into the database. The triggers are listed in the order that they fire.

Quad-Based Trap Placement Data

Trigger Name: TPLACE_UPD_BEF_ROW

Table: TEST_PLACEMENT

Procedures Called: TdataPkg.Get_GM_Sequence_ID

Script File:

Sceolaun -> C:\oracle\trap_triggers\tplace_upd_bef_row.trg

Mothsbane -> D:\trap_files\trap_triggers\tplace_upd_bef_row.trg

ACTION: This trigger fires BEFORE INSERT, UPDATE, OR DELETE on the TEST_PLACEMENT table for each row that is affected. Variables in the TdataPkg package are assigned values from the current row in the TEST_PLACEMENT table. There are no changes made to the table data in order to avoid the dreaded ‘mutating table’ error. The variables will be referenced by the next trigger, TPLACE_INS_UPD_AFT_ROW.

Inserting:

The trigger fires and proceeds through this section when a row is inserted into the TEST_PLACEMENT table. The function TdataPkg.GET_GM_SEQUENCE_ID assigns the next value from the GMSEQ sequence to the ID column. Values are assigned to variables from the row being inserted.

If the record being inserted is from a West Virginia trapper, the variable M_WV_QUAD_CODE may have a value. A lookup is done on the QUAD table to find the associated abbreviation. If a match is found, the abbreviation is assigned to the variable V_LDR_ABBREV. (West Virginia trappers once entered data using a set of numeric quad codes that are different from the quad abbreviations used by other agencies. The codes are listed in the WV_QUAD_CODE column of the QUAD table. This method is not currently being used.)

The next two ‘IF’ statements are here because we needed some creativity in loading data from the GPS records. There is limited space in the data entry fields, so we have had to make several of the character positions serve multiple purposes. The first ‘IF’ statement was used with Trimble Scoutmaster GPS receivers and is no longer used with the Magellan units. I left it in here in case the manufacturers of the Magellan units decide to add a similar feature to their units. The second ‘IF’ statement is still used to indicate that a trap was placed beyond the edge of the target circle.

First IF statement – (IF v_gpsdata = ‘L’ THEN…) The column GPS_DATA was added to the TEST_PLACEMENT table when STS project personnel began to replace opscan forms with GPS units. A ‘Y’ is inserted into this column to indicate that the record was received from a GPS unit. The Trimble Scoutmasters included a feature that indicated the accuracy of the UTM coordinate. The valid entry types were ‘ACU-LOCK’, ‘UNDER 30’, and ‘MANUAL’. (The Magellan GPS receivers do not have this feature.)

With the Scoutmaster, the database assumed that 30 ‘points’ were recorded with every set of UTM coordinates. If the trapper stopped the GPS unit before reaching 30 points, he/she could enter the letter ‘L’ in the 14th position of the GPS record to indicate fewer than 30 points were recorded. The letter ‘L’ was loaded into the GPS_DATA column. This trigger converts the letter ‘L’ to the letter ‘Y’ in the GPS_DATA column and inserts ‘UNDER 30’ into the ENTRY_TYPE column.

Second IF statement – (IF m_outside_target IS NULL AND v_extravar = ‘8’ THEN…) The column OUTSIDE_TARGET in the TEST_PLACEMENT table is used to indicate that a trapper placed a trap further than the desired distance from the target location. The trapper enters the letter ‘B’ in the position after the trap_type code. (This position is also used to report the reason for omitting a site.) The letter ‘B’ is converted to the number 8 and is inserted into the EXTRA_VAR column of the TEST_PLACEMENT table. This ‘IF’ statement sets the OUTSIDE_TARGET column to ‘Y’ and sets the EXTRA_VAR column to NULL.

Updating:

If a row is being updated, the trigger deletes the rows from the ERRORS table with a value in the ROW_ID column that matches the internal ROWID value of the row being updated in the TEST_PLACEMENT table. The trigger also updates rows in the ERROR_SUMMARY table and sets the DATE_OUT column to the SYSDATE where the ID value matches the ID of the row being updated in the TEST_PLACEMENT table.

Deleting:

If a row is being deleted from the TEST_PLACEMENT table, the trigger deletes the appropriate rows from the ERRORS table based on the ROW_ID value. It also updates the ERROR_SUMMARY table and sets the DATE_OUT value to the SYSDATE where the ID matches the ID of the row being updated in the TEST_PLACEMENT table.


Trigger Name: TPLACE_INS_UPD_AFT_ROW

Table: TEST_PLACEMENT

Procedures Called: TDATAPKG (package variables only)

Script File:

Sceolaun -> C:\oracle\trap_triggers\tplace_ins_upd_aft_row.trg

Mothsbane -> D:\trap_files\trap_triggers\tplace_ins_upd_aft_row.trg

ACTION:

This trigger fires AFTER INSERT OR UPDATE on the TEST_PLACEMENT table for each row that is affected. Its only action is to assign the internal row id value of the current row in the TEST_PLACEMENT table to the row_id variable in the TdataPkg package.


Trigger Name: TPLACE_INS_UPD_AFT

Table: TEST_PLACEMENT

Procedures Called: TDATAPKG (package variables only)
QUAD_CONVERT
SITE_VALID
DAY_CHECK
FORMDATE_LATER_THAN_SCAN
TRAPPER_CONVERT
TRAPTYPE_VALID
QUADSITE_UNIQUE
GRIDUTMS_UNIQUE
UTMS_UNIQUE
OMIT_WHY_VALID
UTM_QUAD_CHECK
TRAP_DISTANCE
TRAPS_TOO_CLOSE

Script File:

Sceolaun -> C:\oracle\trap_triggers\tplace_ins_upd_aft.trg

Mothsbane -> D:\trap_files\trap_triggers\tplace_ins_upd_aft.trg

Action:

This trigger fires AFTER INSERT OR UPDATE on the TEST_PLACEMENT table. A boolean variable, NO_ERRORS, is set to true at the beginning of the trigger. If an error is encountered during the processing of the trigger NO_ERRORS will be set to false and control continues with the next command. If at the end of the trigger NO_ERRORS is still true, a row will be inserted into either the PLACEMENT or OMITTED_SITES table based on the value of V_TRAPTYPE. Otherwise, the row will remain in TEST_PLACEMENT with the appropriate error codes entered into the ERRORS and ERROR_SUMMARY tables.

The procedural flow of the trigger is as follows:

First, all variables are declared and assigned a data type. Two cursors are defined. The first (SITES_CUR) selects a row from the SITE_LOCATIONS table based on the values of the variables v_usgs_code and v_site. The second cursor (FIRST_COORDS_ID) searches for previous entries in the FIRST_COORDINATES table.

Following the first BEGIN statement, data values are assigned to the variables from the currently selected record.

The procedure QUAD_CONVERT searches the QUAD table for the appropriate USGS_CODE based on either the quad abbreviation or the West Virginia quad code.

If the site number either equals zero or is missing, the procedure SITE_VALID is called to manage the error.

If there was an error with either the quad code or site number, the trigger raises the exception QUAD_SITE_ERROR and exits without performing any additional validations.

If both the quad code and site number are valid, the trigger continues by opening and fetching from the cursor SITES_CUR. A record will be returned from the SITE_LOCATIONS table if a matching quad and site is found for the values in the variables v_usgs_code and v_site. If no matching record is found, the exception NO_SITE_DATA is raised, records are inserted into the ERRORS and ERROR_SUMMARY tables, and processing ends.

If a record is found in the SITE_LOCATIONS table with a matching quad and site value, processing continues with the following validations.

The variable M_SCAN is filled with the current SYSTEM date that is loaded with the GPS record. The date will be formatted to be compatible with Oracle’s default date format (DD-MON-YYYY).

(Note: The term SCAN is used because data was submitted using optical scan forms when the database was created in 1994. GPS units were first introduced into the project in 1996.)

The procedure DAY_CHECK is called to check for a valid trap placement date. If there are no errors in the placement date, the procedure FORMDATE_LATER_THAN_SCAN is called. This procedure checks for trap placement dates that are later than the data entry dates (m_scan).

The procedure TRAPPER_CONVERT is called to search for the trapper’s initials in the PEOPLE table. Errors are logged if the trapper’s initials are missing. The procedure also compares the agency value in the record from the SITE_LOCATIONS table to the agency value in the record that is found in the PEOPLE table. if these values do not match an error is logged in the ERRORS and ERROR_SUMMARY tables with constraint –20002.

The procedure TRAPTYPE_VALID checks for an entry in the M_TRAPTYPE field. If it is missing, errors are logged in the ERRORS and ERROR_SUMMARY tables through the NULL_TRAPTYPE exception. The code from the GPS record (D, M, or O) is converted as the record is inserted into the TEST_PLACEMENT table. The valid values are DELTA, MILK CARTON, and OMIT. If any other code is entered in the GPS record, it is inserted without any conversion into the trap_type field in the TEST_PLACEMENT table. If invalid values are found, the TRAPTYPE_VALID procedure will insert records into the ERRORS and ERROR_SUMMARY tables with the constraint ‘CHK_TESTPLACE_TRAPTYPE’.

The procedure QUADSITE_UNIQUE looks for an existing quad code and site number. If a match is located in either the PLACEMENT or the OMITTED_SITES table, a ‘duplicate site’ error is logged in the ERRORS and ERROR_SUMMARY tables.

The procedure GRIDUTMS_UNIQUE looks for an existing grid node utm coordinate pair within the zone where the current record is located. If a match is located in either the PLACEMENT or the OMITTED_SITES table, a ‘duplicate grid node utms’ error is logged in the ERRORS and ERROR_SUMMARY tables.

The procedure UTMS_UNIQUE looks for an existing utm coordinate pair within the zone where the current row is located. If a match is located in either the PLACEMENT or the OMITTED_SITES table, a ‘duplicate utms’ error is logged in the ERRORS and ERROR_SUMMARY tables.

If the value in v_traptype is ‘OMIT’, the procedure OMIT_WHY_VALID checks for a valid value from the OMIT_REASONS table. If the variable v_why is null then the exception NULL_OMIT_REASON is raised. If the value in the variable v_why is not found in the OMIT_REASONS table, errors are logged with constraint –20018 through the exception INVALID_OMIT_REASON.

The procedure UTM_QUAD_CHECK performs several validations related to the utm coordinates. The procedure checks for missing values in the grid, utm_east, and utm_north columns. If there are no missing items, the procedure verifies that the grid node utm coordinates in the SITE_LOCATIONS table lie within the limits for the specified quad. If the grid type is reported as ‘random’ the procedure checks the utm coordinates of the trap location instead of the grid node utm coordinates. Errors are logged in the ERRORS and ERROR_SUMMARY tables. NOTE: There is a call to two procedures, ROUND_UTME and ROUND_UTMN. These procedures are no longer used but remain here in case they are needed in the future. These procedures were used previously to calculate the grid node coordinates when a site was not found in the SITE_LOCATIONS table.

The procedure TRAP_DISTANCE reports the distance of the trap from the grid node location. If the trap is located outside of the target circle, the procedure raises the exception OUTSIDE_TARGET. The distance between the target grid node location and the actual trap location is calculated and stored in the variable ‘trap_dist’ (meters). The distance between the trap location and the edge of the target circle is calculated and stored in the variable ‘v_outside_target’ (meters). This value will be inserted into the DISTANCE_OUTSIDE column in the PLACEMENT table.

The procedure TRAPS_TOO_CLOSE checks the PLACEMENT table for any traps that have been placed within 100 meters of the current trap. The OMITTED_SITES table is also checked for any records within 100 meters of the current trap. If any traps or omitted sites are located, a ‘traps too close’ error is logged in the ERRORS and ERROR_SUMMARY tables.

A row is inserted into the FIRST_COORDINATES table in order to store the original utm coordinates that are reported for each site. Since each record is to be stored the first time it enters the database, the cursor FIRST_COORDS_ID is used to search for an existing record. A row is inserted into the FIRST_COORDINATES table if the ID is not found; otherwise, control continues to the next step.

The error flag NO_ERRORS is checked at this point. If one or more errors were encountered the NO_ERRORS variable is now FALSE. No further action will be taken. However, if NO_ERRORS has remained TRUE, the trigger will perform two additional actions. First, the SITE_LOCATIONS table will be updated. A ‘Y’ will be entered into the PLACED column to indicate that the site has been addressed.

Second, a row is inserted into either the PLACEMENT or OMITTED_SITES table. The trigger checks the variable V_TRAPTYPE. If the value is ‘OMIT’, a row is inserted into the OMITTED_SITES table. If the value is either ‘DELTA’ or ‘MILK CARTON’, a row is inserted into the PLACEMENT table.


Trigger Name: OMIT_INSERT_AFT

Table: OMITTED_SITES

Procedures Called: No procedures

Script File:

Sceolaun -> C:\oracle\trap_triggers\omit_insert_aft.trg

Mothsbane -> D:\trap_files\trap_triggers\omit_insert_aft.trg

Action:

This trigger fires when a row is INSERTED into the OMITTED_SITES table. It deletes a row from the TEST_PLACEMENT table if there is an ID value that matches the ID of the row being inserted into OMITTED_SITES. By coding this action into a trigger, it ensures that the row is inserted into the OMITTED_SITES table before the row is deleted from TEST_PLACEMENT.


Trigger Name: PLACE_INSERT_AFT

Table: PLACEMENT

Procedures Called: No procedures

Script File:

Sceolaun -> C:\oracle\trap_triggers\place_insert_aft.trg

Mothsbane -> D:\trap_files\trap_triggers\place_insert_aft.trg

Action:

This trigger fires when a row is INSERTED into the PLACEMENT table. It deletes a row from the TEST_PLACEMENT table if there is an ID value that matches the ID of the row being inserted into PLACEMENT. By coding this action into a trigger, it ensures that the row is inserted into the PLACEMENT table before the row is deleted from TEST_PLACEMENT.


Quad-Based Trap Inspection Data

Trigger Name: TINSPECT_UPD_BEF_ROW

Table: TEST_INSPECTION

Procedures Called: TdataPkg.Get_GM_Sequence_ID

Script File:

Sceolaun -> C:\oracle\trap_triggers\tinspect_upd_bef_row.trg

Mothsbane -> D:\trap_files\trap_triggers\tinspect_upd_bef_row.trg

ACTION:

This trigger fires BEFORE INSERT, UPDATE, or DELETE on the TEST_INSPECTION table. This trigger fires for each row that is affected. Variables in the TdataPkg package are assigned values from the current row in the TEST_INSPECTION table. There are no changes made to the table data in order to avoid the dreaded ‘mutating table’ error. The variables will be referenced by the next trigger, TINSPECT_INS_UPD_AFT_ROW.

Inserting:

The trigger fires and proceeds through this section when a row is inserted into the TEST_INSPECTION table. The function TdataPkg.GET_GM_SEQUENCE_ID assigns the next value from the GMSEQ sequence to the ID column. Values are assigned to variables from the row being inserted.

If the record being inserted is from a West Virginia trapper, the variable M_WV_QUAD_CODE will have a value. A lookup is done on the QUAD table to find the associated abbreviation. If a match is found, the abbreviation is assigned to the variable V_LDR_ABBREV. (West Virginia trappers enter data using a set of numeric quad codes that are different from the quad abbreviations used by other agencies. The codes are listed in the WV_QUAD_CODE column of the QUAD table.)

The next three ‘IF’ statements are here because we needed some creativity in loading data from the GPS records. There is limited space in the data entry fields, so we have had to make several of the character positions serve multiple purposes. The first ‘IF’ statement was used with Trimble Scoutmaster GPS receivers and is no longer used with the Magellan units. I left it in here in case the manufacturers of the Magellan units decide to add a similar feature to their units.

First IF statement – (IF v_gpsdata = ‘L’ THEN…) The column GPS_DATA was added to the TEST_PLACEMENT table when STS project personnel began to replace opscan forms with GPS units. A ‘Y’ is inserted into this column to indicate that the record was received from a GPS unit. The Trimble Scoutmasters included a feature that indicated the accuracy of the UTM coordinate. The valid entry types were ‘ACU-LOCK’, ‘UNDER 30’, and ‘MANUAL’. (The Magellan GPS receivers do not have this feature.) With the Scoutmaster, the database assumed that 30 ‘points’ were recorded with every set of UTM coordinates. If the trapper stopped the GPS unit before reaching 30 points, he/she could enter the letter ‘L’ in the 14th position of the GPS record to indicate fewer than 30 points were recorded. The letter ‘L’ was loaded into the GPS_DATA column. This trigger converts the letter ‘L’ to the letter ‘Y’ in the GPS_DATA column and inserts ‘UNDER 30’ into the ENTRY_TYPE column.

The second ‘IF’ statement (ELSIF v_gpsdata IS NULL THEN…) simply ensures that the GPS_DATA column contains a ‘Y’.

The third ‘IF’ statement (ELSIF TO_NUMBER(v_gpsdata) >= 0 THEN…) allows us to use the last two characters in the GPS record (positions 19 and 20) for two purposes. A trapper can record a catch value during a routine trap inspection, or a supervisor can enter a QC failure code during a ‘less-than-perfect’ quality control inspection. SQL*Loader will not allow one position in the input file to be designated as character data in one case and numeric data in another. So, we assign that position (20) to a character data type and convert to a numeric data type when necessary. There are three possible scenarios:

  1. A trapper enters a routine trap catch. The trapper is required to enter three digits for the catch value. The first two digits (positions 18 and 19) are inserted into the CATCH column, and the third digit (position 20) is inserted into the GPS_DATA column. This trigger multiplies the CATCH value by 10, then converts the value in GPS_DATA to a numeric value and adds it to the CATCH value. This final value will be inserted into the CATCH column in the INSPECTION table.
  2. A supervisor enters a passing QC inspection. He/she enters one digit into the CATCH column (position 18) and the letter ‘P’ into the FIELD_CHECK column (position 19). Position 20 is left blank, and a null value is assigned to the GPS_DATA column.
  3. A supervisor enters a failing QC inspection. He/she enters one digit into the CATCH column (position 18), the letter ‘F’ into the FIELD_CHECK column (position 19), and a QC failure code into the QC_FAIL column (position 20).

Updating:

If a row is being updated, the trigger deletes the rows from the ERRORS table with a value in the ROW_ID column that matches the internal ROWID value of the row being updated in the TEST_INSPECTION table. The trigger also updates rows in the ERROR_SUMMARY table and sets the DATE_OUT column to the SYSDATE where the ID value matches the ID of the row being updated in the TEST_INSPECTION table.

Deleting:

If a row is being deleted from the TEST_INSPECTION table, the trigger deletes the appropriate rows from the ERRORS table based on the ROW_ID value. It also updates the ERROR_SUMMARY table and sets the DATE_OUT value to the SYSDATE where the ID matches the ID of the row being updated in the TEST_INSPECTION table.


Trigger Name: TINSPECT_INS_UPD_AFT_ROW

Table: TEST_INSPECTION

Procedures Called: TDATAPKG (variables only)

Script File:

Sceolaun -> C:\oracle\trap_triggers\tinspect_ins_upd_aft_row.trg

Mothsbane -> D:\trap_files\trap_triggers\tinspect_ins_upd_aft_row.trg

ACTION:

This trigger fires AFTER INSERT OR UPDATE on the TEST_INSPECTION table for each row that is affected. Its only action is to assign the internal row id value of the current row in the TEST_INSPECTION table to the row_id variable in the TdataPkg package.


Trigger Name: TINSPECT_INS_UPD_AFT

Table: TEST_INSPECTION

Procedures Called: TDATAPKG
DAY_CHECK
FORMDATE_LATER_THAN_SCAN
QUAD_CONVERT
SITE_VALID
OMITTED_SITE
PLACED_SITE
TRAPPER_VALID
INSPECT_UNIQUE
UTMS_UNIQUE
UTM_QUAD_CHECK
TRAP_DISTANCE
TRAPS_TOO_CLOSE
QC_VALID
VISIT_FINAL
VISIT_VALID
TRAP_CONDITION
CATCH_VALID
PLACEMENT_UPD

Script File:

Sceolaun -> C:\oracle\trap_triggers\tinspect_ins_upd_aft.trg

Mothsbane -> D:\trap_files\trap_triggers\tinspect_ins_upd_aft.trg

ACTION:

This trigger fires AFTER INSERT OR UPDATE on the TEST_INSPECTION table. A boolean variable, NO_ERRORS, is set to true at the beginning of the trigger. If an error is encountered during the processing of the trigger NO_ERRORS will be set to false and control continues with the next command. If at the end of the trigger NO_ERRORS is still true, a row will be inserted into the INSPECTION table. Otherwise, the row will remain in the TEST_INSPECTION table with the appropriate error codes inserted into the ERRORS and ERROR_SUMMARY tables.

The procedural flow of the trigger is as follows:

First, all variables are declared and assigned a data type. Two cursors are defined. The first (SITES_CUR) selects the ID and AGENCY from the SITE_LOCATIONS table and the UTM ZONE from the QUAD table for records matching the values of the variables v_usgs_code and v_site. The second cursor (TOT_CATCH) selects the total_catch value from the PLACEMENT table where the quad and site match the values of the v_usgs_code and m_site variables.

Following the first BEGIN statement, data values are assigned to the variables from the currently selected record. If the table is being updated, the ID value is assigned from the o_id variable in the TdataPkg package.

The variable M_SCAN is filled with the current SYSTEM date that is loaded with the GPS record. The date will be formatted to be compatible with Oracle’s default date format (DD-MON-YYYY).

(Note: The term SCAN is used because data was submitted using optical scan forms when the database was created in 1994. GPS units were first introduced into the project in 1996.)

The procedure DAY_CHECK is called to check for a valid trap inspection date. If there are no errors in the inspection date, the procedure FORMDATE_LATER_THAN_SCAN is called. This procedure checks for trap inspection dates that are later than the data entry dates (m_scan).

The procedure QUAD_CONVERT searches the QUAD table for the appropriate USGS_CODE based on either the quad abbreviation in the variable v_ldr_abbrev or the West Virginia quad code in v_wv_quad_code.

If the site number either equals zero or is missing, the procedure SITE_VALID is called to manage the error.

Next open the SITES_CUR cursor and select the following values into variables: SITE_LOCATIONS.ID, SITE_LOCATIONS.AGENCY, QUAD.ZONE. The cursor is closed if no record is returned; otherwise, the PREDETERMINED_SITE variable is set to true.

If there were no errors with either the quad code or site number, then the OMITTED_SITE procedure is called. This procedure searches the OMITTED_SITES table for a record matching the quad and site values in the variables v_usgs_code and v_site. There should be no inspections since there are no traps placed at omitted sites. An error will be generated if a row is found in the OMITTED_SITES table. If there are no matches in the OMITTED_SITE table, the PLACED_SITE procedure is called. This procedure searches for a row in the PLACEMENT table based on the values in the v_usgs_code and v_site variables. A row with a matching quad and site value must exist in the PLACEMENT table before a row can be inserted into the INSPECTION table. An error will be generated if no match is found in the PLACEMENT table.

The procedure TRAPPER_VALID is called to search for the trapper’s initials in the PEOPLE table. Errors are logged if the trapper’s initials are missing. The procedure also compares the agency value in the record from the SITE_LOCATIONS table to the agency value in the record that is found in the PEOPLE table. If these values do not match an error is logged in the ERRORS and ERROR_SUMMARY tables with constraint –20002.

If there are no quad or site errors, the procedure INSPECT_UNIQUE checks for an existing record in the INSPECTION table. Only one record with the same quad, site, inspection date, and field check value may be entered. Errors are logged in the ERRORS and ERROR_SUMMARY tables if a record exists with matching values for all four columns.

The next section was developed for use with the Trimble Scoutmaster GPS receivers. The Scoutmaster units enabled the trapper to record UTM coordinates with various levels of accuracy. The most accurate data entry type is ACU-LOCK, followed by UNDER 30, MANUAL, and OPSCAN. The variable M_REPLACE is originally set to FALSE. The variable M_ENTRYTYPE is assigned the value of the current ENTRY_TYPE column from the TEST_INSPECTION table. The variable N_ENTRYTYPE is assigned the value of the ENTRY_TYPE column from the row with a matching quad and site value in the PLACEMENT table. The variable M_REPLACE will be set to TRUE if the value of M_ENTRYTPE is considered to be more accurate than the value of N_ENTRYTYPE. If M_REPLACE is TRUE, the UTM coordinates in the current TEST_INSPECTION record are checked more closely. The following procedures are called: utms_unique, utm_quad_check, trap_distance, and traps_too_close. If there are no errors, the UTM coordinates in the PLACEMENT table are replaced with the UTM coordinates from the current TEST_INSPECTION record. The ENTRY_TYPE column in the PLACEMENT record will be updated with the value from the TEST_INSPECTION record (M_ENTRYTYPE). The valid value for ENTRY_TYPE when entering data from the Magellan units is MAGELLAN. The variable M_REPLACE will remain FALSE, so processing will continue with the call to the QC_VALID procedure.

The QC_VALID procedure checks for valid values in the FIELD_CHECK and QC_FAIL columns. The valid values for the FIELD_CHECK column are F, N, P, or NULL. If the value of the FIELD_CHECK column is F, then the QC_FAIL column must contain a valid value from the QC_FAIL_REASONS table. Errors are logged in the ERRORS and ERROR_SUMMARY tables if these conditions are not met.

If the value of the M_VISIT variable is ‘FINAL’, the VISIT_FINAL procedure is called to check for two conditions. The VISIT column must not be NULL, and there must exist only one record for each quad and site with a value of ‘FINAL’ in the VISIT column and a value of ‘N’ in the FIELD_CHECK column. A trapper may remove a trap only one time; however, a supervisor may check the site multiple times to be sure the trapper removed the trap. There may exist multiple records with the same quad and site with a value of ‘FINAL’ in the VISIT column and a value of ‘P’ or ‘F’ in the FIELD_CHECK column to indicate a QC inspection.

The procedure VISIT_VALID checks for a valid value in the VISIT column (V_VISIT). Valid entries are ‘MIDSEASON’ or ‘FINAL’, and the column may not be null. The procedure also checks the value in the DAY column (V_DAY) to ensure that all midseason inspections occur prior to a final inspection. It is assumed that the trap is removed from the field at the earliest final visit. Errors are logged in the ERRORS and ERROR_SUMMARY tables.

The next section regarding DEPTH and EXTRA VARIABLES has been commented out. This section was used for a short time to help trappers record moth catches in areas where there were very high gypsy moth populations. The trapper would install milk carton traps and measure the depth of the moths in the traps instead of counting individual moths. The formula could then be applied to convert the depth to a catch value. This method of recording trap catch values is no longer used. I kept it in the trigger in case it needs to be revived at some point.

The procedure TRAP_CONDITION checks for a valid value in the CONDITION column (M_TRAP_COND). The column many not be null, and the valid values are: GOOD, DAMAGED, INACCESSIBLE, MISSING. The database assumes that a trapper can physically touch a trap and look inside the trap for moths when the condition is reported as ‘GOOD’ or ‘DAMAGED’. If a trapper returns to a trap site for an inspection and is unable to either get access to the exact location or is unable to find the trap, the condition should be entered as ‘MISSING’ or ‘INACCESSIBLE’. The condition should be entered as ‘MISSING’ if the trap is so severely damaged that moths cannot be counted. Errors are logged in the ERRORS and ERROR_SUMMARY tables if the CONDITION column is NULL or if any other values are entered.

If there are no errors in the CONDITION column, then the procedure CATCH_VALID is called. This procedure checks for the following situations:

    1. If the value of CONDITION (V_TRAP_COND) is ‘MISSING’ or ‘INACCESSIBLE’, then CATCH (M_CATCH) must be NULL. (The trapper cannot count moths if he/she cannot look inside of the trap.)
    2. If the value of CONDITION (V_TRAP_COND) is ‘GOOD’ or ‘DAMAGED’, then CATCH (M_CATCH) must be greater than or equal to zero. (The trapper must report a moth catch of zero or greater.)

Errors are logged in the ERRORS and ERROR_SUMMARY tables.

If at this point the error flag (NO_ERRORS) remains true, then each of the column values are assigned to a table record. This record is inserted into the INSPECTION table.

The procedure PLACEMENT_UPD is called to update the parent row in the PLACEMENT table. The first IF statement will be ignored since M_REPLACE will be FALSE while the Magellan GPS units are in use. If M_REPLACE is TRUE, the UPDATE statement will set the UTM coordinates to the values from the inspection record (M_UTME and M_UTMN). It will also set the ENTRY_TYPE column to the value in M_ENTRYTYPE from the inspection record, and it will update the DISTANCE_OUTSIDE column with the figure that was calculated using the UTM coordinates in the inspection record (V_OUTSIDE_TARGET).

The TOTAL_CATCH value is updated in the PLACEMENT table based on the previous TOTAL_CATCH value (S_CATCH) in the PLACEMENT record and the CATCH value in the new inspection record (V_CATCH). The TOTAL_CATCH value in the PLACEMENT table is indicated as follows:

-2 = no inspection (default value when row inserted into PLACEMENT table)

-1 = site inspected by trap was missing/inaccessible at all inspections

0+ = total moth catch for the site

The TOTAL_CATCH column value will be updated as follows:

  1. Change TOTAL_CATCH from –2 to –1 only if this is the first inspection and the trap is missing/inaccessible.
  2. Recalculate TOTAL_CATCH only if the catch value is > 0. If TOTAL_CATCH = -1 or –2, set TOTAL_CATCH to 0 before adding new catch value. (A null catch value indicates a missing/inaccessible trap.)

Trigger Name: INSPECT_INSERT_AFT

Table: INSPECTION

Procedures Called: None

Script File:

Sceolaun -> C:\oracle\trap_triggers\inspect_insert_aft_trg.sql

Mothsbane -> D:\trap_files\trap_triggers\inspect_insert_aft_trg.sql

ACTION:

This trigger fires AFTER a row is INSERTED into the INSPECTION table. It deletes a row from the TEST_INSPECTION table if there is an ID value that matches the ID of the row being inserted into the INSPECTION table.


Procedure Name: DAY_CHECK

Called By: PLACEMENT_AFT trigger

INSPECT_AFT_UPD trigger

Procedures Called: NONE

Action:

This procedure checks for a valid trap placement/inspection date and formats the date for entry into the appropriate table. If the date is null, the DAY_MISSING exception is raised. If the date exists but is not a proper date, the INVALID_DAY exception is raised. If the date is valid, it is formatted for entry into the appropriate table and stored in the variable V_DAY.


Procedure Name: FORMDATE_LATER_THAN_SCAN

Called By: PLACEMENT_AFT

INSPECT_AFT_UPD

Procedures Called: NONE

Actions:

The date entered on the form or into the GPS receiver is stored in the variable V_DAY. If V_DAY occurs later than the date the forms are scanned (C_SCAN), then the exception DATE_LATER_THAN_SCAN is raised.


Procedure Name: QUAD_CONVERT

Called By: PLACEMENT_AFT

INSPECT_AFT_UPD

Procedures Called: NONE

Action:

This procedure checks for a valid quad abbreviation or valid WV quad code. The NULL_QUAD exception is raised if both values are null. If either the quad abbreviation or the WV quad code is present, the cursor QUAD_PROC searches for the corresponding USGS code in the QUAD table. If this is a valid code, the USGS code will be stored in the variable V_USGS_CODE. If no match is found, the exception INVALID_ABBREVIATION is raised. Errors are inserted into the ERRORS and ERROR_SUMMARY tables.


Procedure Name: SITE_VALID

Called By: PLACEMENT_AFT

INSPECT_AFT_UPD

Procedures Called: NONE

Action:

The SITE_VALID procedure looks for a site number greater than zero. If there are no errors, the site number is stored in the variable V_SITE. The exception NULL_SITE is raised if the site number is missing, and the exception INVALID_SITE is raised if the site number equals zero. Errors are inserted into the ERRORS and ERROR_SUMMARY tables.


Procedure Name: TRAPPER_CONVERT

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

The cursor TRAPPER_CUR selects the ID from the PEOPLE column based on the trapper’s initials in the variable V_TRAPPER_INIT. If the initials are located in the PEOPLE table, the trapper’s ID is stored in the variable M_TRAPPER_ID. If the trapper’s initials are missing, the exception NULL_TRAPPER is raised. The exception INVALID_TRAPPER is raised if the initials are not found in the PEOPLE table. Errors are inserted into the ERRORS and ERROR_SUMMARY tables.


Procedure Name: TRAPTYPE_VALID

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

If the trap type is missing, the exception NULL_TRAPTYPE is raised and errors are written to the ERRORS and ERROR_SUMMARY tables. Otherwise, the trap type is stored in the variable V_TRAPTYPE.


Procedure Name: QUADSITE_UNIQUE

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

The cursor OMIT_CUR searches for an existing site in the OMITTED_SITES table based on the values stored in the variables V_USGS_CODE and V_SITE. If an existing record is located, the exception DUP_OMITTED_QUADSITE is raised. The cursor PLACE_CUR performs the same search on the PLACEMENT table. The exception DUP_PLACEMENT_QUADSITE is raised if an existing record is located. Errors are inserted into the ERRORS and ERROR_SUMMARY tables. If no records are located control returns to the calling procedure, PLACEMENT_AFT.


Procedure Name: UTMS_UNIQUE

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

The cursor OMIT_CUR searches for an existing site in the OMITTED_SITES table based on the utm coordinates stored in the variables M_UTME and M_UTMN. If an existing record is located, the exception DUP_OMITTED_UTMS is raised. The cursor PLACE_CUR performs the same search on the PLACEMENT table. The exception DUP_PLACEMENT_UTMS is raised if an existing record is located. Errors are inserted into the ERRORS and ERROR_SUMMARY tables. If no records are located, control returns to the calling procedure, PLACEMENT_AFT.


Procedure Name: GRIDUTMS_UNIQUE

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

The cursor OMIT_CUR searches for an existing site in the OMITTED_SITES table based on the grid node utm coordinates stored in the variables M_UTME_GRID and M_UTMN_GRID. If an existing record is located, the exception DUP_OMITTED_GRIDNODE is raised. The cursor PLACE_CUR performs the same search on the PLACEMENT table. The exception DUP_PLACEMENT_GRIDNODE is raised if an existing record is located. Errors are inserted into the ERRORS and ERROR_SUMMARY tables. If no records are located, control returns to the calling procedure, PLACEMENT_AFT.


Procedure Name: UTM_QUAD_CHECK

Called By: PLACEMENT_AFT

Procedures Called: ROUND_UTME

ROUND_UTMN

Action:

This procedure checks the utm coordinates to ensure that the site is located within the reported quad. If the grid type is reported as either ‘Random’ or ‘Other’, the utm coordinates are checked. Otherwise, the grid node utms are checked. If the grid type is missing, the exception NULL_GRID is raised. Errors are inserted into the ERRORS and ERROR_SUMMARY tables. If either one of the utm coordinates is missing, one of the following exceptions is raised - NULL_UTME or NULL_UTMN. If both of the utm coordinates are missing the exception NULL_UTMS is raised. Errors are inserted into the ERRORS and ERROR_SUMMARY tables. If necessary, the procedures ROUND_UTME and ROUND_UTMN are called in order to calculate the grid node coordinates. If the BOOLEAN variables Y_UTME and Y_UTMN are both true, the coordinates are within the quad boundaries. However, if either Y_UTME or Y_UTMN is false, the exception OUTSIDE_QUAD is raised. Errors are inserted into the ERRORS and ERROR_SUMMARY tables.


Procedure Name: ROUND_UTME

Called By: UTM_QUAD_CHECK

Procedures Called: NONE

Action:

This procedure checks the grid type stored in the variable V_GRID. If the grid type equals ‘RANDOM’ or ‘OTHER’, the grid node utm easting will be equal to the utm easting stored in the variable M_UTME. Otherwise, the following formula is used to calculate the grid node easting value:

m_utme_grid := TRUNC((m_utme/v_roundist)+0.5)*v_roundist

The rounding distance (V_ROUNDIST) is stored in the ROUNDING_DISTANCE column of the GRID table and is selected based on the grid type (V_GRID). The grid node utm easting coordinate M_UTME_GRID is returned to the UTM_QUAD_CHECK procedure.


Procedure Name: ROUND_UTMN

Called By: UTM_QUAD_CHECK

Procedures Called: NONE

Action:

This procedure checks the grid type stored in the variable V_GRID. If the grid type equals ‘RANDOM’ or ‘OTHER’, the grid node utm northing will be equal to the utm northing stored in the variable M_UTMN. Otherwise, the following formula is used to calculate the grid node northing value:

m_utmn_grid := TRUNC((m_utmn/v_roundist)+0.5)*v_roundist

The rounding distance (V_ROUNDIST) is stored in the ROUNDING_DISTANCE column of the GRID table and is selected based on the grid type (V_GRID). The grid node utm northing coordinate M_UTMN_GRID is returned to the UTM_QUAD_CHECK procedure.


Procedure Name: TRAP_DISTANCE

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

The cursor TARGET_CUR selects from the GRID table the distance to be used as the radius of the target circle. If the requested grid type is not found in the GRID table, the exception INVALID_GRID is raised. If the grid is okay, the absolute value of the distance between the grid node and location coordinates is stored in the variables B_UTME and B_UTMN. The BOOLEAN variables X_UTME and X_UTMN will flag coordinates that are outside the target circle. If either X_UTME or X_UTMN are false, the exception OUTSIDE_TARGET is raised. The distance between the trap location and the grid node is stored in the variable TRAP_DIST. The variable V_OUTSIDE_TARGET contains ]the distance that the trap is located outside the target circle.


Procedure Name: TRAPS_TOO_CLOSE

Called By: PLACEMENT_AFT

Procedures Called: NONE

Action:

This procedure checks for traps that are placed too close to the current location. The cursor DISTANCE_CUR selects rows from the PLACEMENT table where the utm coordinates are fewer than 72 meters from the current record. Using the distance formula, d=SQRT(POWER((x2-x1),2)+POWER((y2-y1),2)), the easting AND northing coordinates of another trap must be at least 72 meters from another trap. The exception TRAPS_TOO_CLOSE is raised if a row is found, and errors are inserted into the ERRORS and ERROR_SUMMARY tables. If no rows are selected, control returns to the calling procedure.

   
  © 2012 Slow the Spread Foundation, Inc.  

Page developed and maintained by Virginia Tech

  Contact Us