ALTER TRIGGER "OCCASIONAL_OBS_BEFORE_INSERT"
ACTIVE BEFORE INSERT POSITION 1
AS
  DECLARE VARIABLE HAS_STR VARCHAR(250);
BEGIN
  IF (NEW.OBSERVATION_RELATION IS NULL) THEN
    EXECUTE PROCEDURE GET_OBS_RELATION_POSITION NEW.PA_ID RETURNING_VALUES NEW.OBSERVATION_RELATION;
  HAS_STR = NEW.PA_ID || NEW.OBSERVER_NAME || EXTRACT(YEAR FROM NEW.DATE_TIME) || EXTRACT(MONTH FROM NEW.DATE_TIME) || EXTRACT(DAY FROM NEW.DATE_TIME)  || EXTRACT(HOUR FROM NEW.DATE_TIME) || EXTRACT(MINUTE FROM NEW.DATE_TIME) || EXTRACT(SECOND FROM NEW.DATE_TIME) || NEW.OBSERVATION || NEW.OBSERVATION_CODE;
  EXECUTE PROCEDURE  ELF_HASH HAS_STR RETURNING_VALUES NEW.OCC_ID;
END

ALTER PROCEDURE "OCCASIONAL_OBS_INSERT"
(
  "PA_ID" VARCHAR(6),
  "WAYPOINT_EASTING" FLOAT,
  "WAYPOINT_NORTHING" FLOAT,
  "OBSERVATION_TIME" TIMESTAMP,
  "OBSERVATION_RELATION" INTEGER,
  "TOTAL" INTEGER,
  "ADULT_MALES" INTEGER,
  "ADULT_FEMALES" INTEGER,
  "TOTAL_YOUNG" INTEGER,
  "OBSERVER" VARCHAR(40),
  "SOURCE" VARCHAR(4),
  "OBSERVATION_QUALITY" VARCHAR(5),
  "OBS_REMARKS_RELATION" VARCHAR(20)
)
RETURNS
(
  "WAYPOINT" INTEGER
)
AS
  DECLARE VARIABLE HAS_STR VARCHAR(250);
  DECLARE VARIABLE OBS VARCHAR(30);
  DECLARE VARIABLE OBS_CODE VARCHAR(20);
BEGIN
  SELECT a.OBSERVATION, b.OBS_CODE
  FROM lk_observations a, lk_obs_codes b
  WHERE a.OBS_CODE_RELATION=b.OBS_CODE_RELATION AND b.OBSERVATION_RELATION= :OBSERVATION_RELATION
  INTO :OBS, :OBS_CODE;
  HAS_STR = PA_ID || OBSERVER || EXTRACT(YEAR FROM OBSERVATION_TIME) || EXTRACT(MONTH FROM OBSERVATION_TIME) || EXTRACT(DAY FROM OBSERVATION_TIME)  || EXTRACT(HOUR FROM OBSERVATION_TIME) || EXTRACT(MINUTE FROM OBSERVATION_TIME) || EXTRACT(SECOND FROM OBSERVATION_TIME) || OBS || OBS_CODE;
  EXECUTE PROCEDURE  ELF_HASH HAS_STR RETURNING_VALUES WAYPOINT;
  INSERT INTO OCCASIONAL_OBSERVATIONS (PA_ID, OCC_ID, WAYPOINT_EASTING, WAYPOINT_NORTHING, DATE_TIME, OBSERVATION_RELATION, TOTAL, ADULT_MALES, ADULT_FEMALES, TOTAL_YOUNG, OBSERVER_NAME, SRC_LOCATION, OBSERVATION_QUALITY, OBS_REMARKS_RELATION)
  VALUES (:PA_ID, :WAYPOINT, CAST(:WAYPOINT_EASTING AS INTEGER), CAST(:WAYPOINT_NORTHING AS INTEGER), :OBSERVATION_TIME, :OBSERVATION_RELATION, :TOTAL, :ADULT_MALES, :ADULT_FEMALES, :TOTAL_YOUNG, :OBSERVER, :SOURCE, :OBSERVATION_QUALITY, :OBS_REMARKS_RELATION);
END

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='OBSERVER_NAME' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='OBSERVATION_QUALITY' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='OBSERVATION_RELATION' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='TOTAL' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='SRC_LOCATION' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';

UPDATE RDB$RELATION_FIELDS
SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME='OBSERVATION_QUALITY' AND RDB$RELATION_NAME='OCCASIONAL_OBSERVATIONS';