REM Tides and Water Levels Benchmarks Data Base
REM LATLON TRIGGER
SET VERIFY OFF
SET PAGESIZE 1000

CREATE OR REPLACE TRIGGER LATLON
BEFORE INSERT OR UPDATE ON TWL_STATION FOR EACH ROW
DECLARE
LATDEG NUMBER (4);
LATMIN NUMBER (4);
LATSEC NUMBER (6,2);
NEW_LATITUDE NUMBER (11,6);
OLD_LATITUDE NUMBER (11,6);
LAT NUMBER (11,6);
LONDEG NUMBER (4);
LONMIN NUMBER (4);
LONSEC NUMBER (6,2);
LON NUMBER (11,6);
NEW_LONGITUDE NUMBER (11,6);
OLD_LONGITUDE NUMBER (11,6);
BEGIN

<<CHECK_LATITUDE>>
NULL;

OLD_LATITUDE := :OLD.LATITUDE;
NEW_LATITUDE := :NEW.LATITUDE;

IF OLD_LATITUDE IS NULL AND NEW_LATITUDE IS NULL --NEW ENTRY
THEN
:NEW.LATITUDE :=
:NEW.LAT_DEGREES + 
(:NEW.LAT_MINUTES/60) +
(:NEW.LAT_SECONDS/(60*60));
IF :NEW.LAT_HEM != 'N'
THEN
:NEW.LATITUDE := :NEW.LATITUDE *(-1);
END IF;
 END IF;

IF OLD_LATITUDE IS NOT NULL AND NEW_LATITUDE IS NULL ---ERASE
THEN
:NEW.LAT_DEGREES := NULL;
:NEW.LAT_MINUTES := NULL;
:NEW.LAT_SECONDS := NULL;
:NEW.LAT_HEM     := 'N';
END IF;

IF OLD_LATITUDE IS NULL AND NEW_LATITUDE IS NOT NULL --ENTER BY LATITUDE
THEN 
LAT := ABS(:NEW.LATITUDE);
LATDEG := TRUNC(LAT);
LATMIN := TRUNC((LAT-LATDEG)*60);
LATSEC := (LAT - LATDEG - (LATMIN/60))*60*60;
:NEW.LAT_DEGREES := LATDEG;
:NEW.LAT_MINUTES := LATMIN;
:NEW.LAT_SECONDS := LATSEC;
IF :NEW.LATITUDE < 0
THEN
:NEW.LAT_HEM := 'S';
ELSE
:NEW.LAT_HEM := 'N';
END IF;
END IF;

IF OLD_LATITUDE IS NOT NULL AND NEW_LATITUDE IS NOT NULL
AND OLD_LATITUDE != NEW_LATITUDE ---CHANGE BY LAT
THEN
LAT := :NEW.LATITUDE;
LATDEG := TRUNC(LAT);
LATMIN := TRUNC((LAT-LATDEG)*60);
LATSEC := (LAT - LATDEG - (LATMIN/60))*60*60;
:NEW.LAT_DEGREES := LATDEG;
:NEW.LAT_MINUTES := LATMIN;
:NEW.LAT_SECONDS := LATSEC;
IF :NEW.LATITUDE < 0
THEN
:NEW.LAT_HEM := 'S';
ELSE
:NEW.LAT_HEM := 'N';
END IF;
END IF;

IF OLD_LATITUDE IS NOT NULL AND NEW_LATITUDE IS NOT NULL
AND OLD_LATITUDE = NEW_LATITUDE
THEN
:NEW.LATITUDE :=
:NEW.LAT_DEGREES + 
(:NEW.LAT_MINUTES/60) +
(:NEW.LAT_SECONDS/(60*60));
IF :NEW.LAT_HEM != 'N'
THEN
:NEW.LATITUDE := :NEW.LATITUDE *(-1);
END IF;
END IF;


<<CHECK_LONGITUDE>>
NULL;


OLD_LONGITUDE := :OLD.LONGITUDE;
NEW_LONGITUDE := :NEW.LONGITUDE;

IF OLD_LONGITUDE IS NULL AND NEW_LONGITUDE IS NULL --NEW ENTRY
THEN
:NEW.LONGITUDE := 
:NEW.LONG_DEGREES + 
(:NEW.LONG_MINUTES/60) +
(:NEW.LONG_SECONDS/(60*60));
IF :NEW.LONG_HEM != 'E'
THEN
:NEW.LONGITUDE := :NEW.LONGITUDE *(-1);
END IF;
END IF;

IF OLD_LONGITUDE IS NOT NULL AND NEW_LONGITUDE IS NULL ---ERASE
THEN
:NEW.LONG_DEGREES := NULL;
:NEW.LONG_MINUTES := NULL;
:NEW.LONG_SECONDS := NULL;
:NEW.LONG_HEM     := 'W';
END IF;

IF OLD_LONGITUDE IS NULL AND NEW_LONGITUDE IS NOT NULL --ENTER BY LONGITUDE
THEN 
LON := :NEW.LONGITUDE;
LONDEG := TRUNC(LON);
LONMIN := TRUNC((LON-LONDEG)*60);
LONSEC := (LON - LONDEG - (LONMIN/60))*60*60;
:NEW.LONG_DEGREES := LONDEG;
:NEW.LONG_MINUTES := LONMIN;
:NEW.LONG_SECONDS := LONSEC;
IF :NEW.LONGITUDE < 0
THEN
:NEW.LONG_HEM := 'W';
ELSE
:NEW.LONG_HEM := 'E';
END IF;
END IF;

IF OLD_LONGITUDE IS NOT NULL AND NEW_LONGITUDE IS NOT NULL
AND OLD_LONGITUDE != NEW_LONGITUDE ---CHANGE BY LONG
THEN
LON := :NEW.LONGITUDE;
LONDEG := TRUNC(LON);
LONMIN := TRUNC((LON-LONDEG)*60);
LONSEC := (LON - LONDEG - (LONMIN/60))*60*60;
:NEW.LONG_DEGREES := LONDEG;
:NEW.LONG_MINUTES := LONMIN;
:NEW.LONG_SECONDS := LONSEC;
IF :NEW.LONGITUDE < 0
THEN
:NEW.LONG_HEM := 'W';
ELSE
:NEW.LONG_HEM := 'E';
END IF;
END IF;

IF OLD_LONGITUDE IS NOT NULL AND NEW_LONGITUDE IS NOT NULL
AND OLD_LONGITUDE = NEW_LONGITUDE
THEN
:NEW.LONGITUDE :=
:NEW.LONG_DEGREES + 
(:NEW.LONG_MINUTES/60) +
(:NEW.LONG_SECONDS/(60*60));
IF :NEW.LONG_HEM != 'E'
THEN
:NEW.LONGITUDE := :NEW.LONGITUDE *(-1);
END IF;
END IF;


<<END_OF_LATLON>>
NULL;

END;
/


SET VERIFY ON
SET PAGESIZE 1000