REM UNDER CONSTRUCTION

REM Tides and Water Levels Benchmarks Data Base
REM MANAGEMENT TRIGGER

CREATE OR REPLACE TRIGGER 
BEFORE INSERT ON &TABLEX FOR EACH ROW
DECLARE
DUMMY INTEGER;
BEGIN
:NEW.DBE_TIME := SYSDATE;
:NEW.DBU_TIME := SYSDATE;
END;
/
CREATE OR REPLACE TRIGGER 
BEFORE UPDATE ON &TABLEX FOR EACH ROW
DECLARE
DUMMY INTEGER;
BEGIN
:NEW.DBE_TIME := :OLD.DBE_TIME; --ENSURES INSERT DATE NEVER CHANGED
:NEW.DBU_TIME := SYSDATE;
END;
/

CREATE OR REPLACE TRIGGER ACTIVATE
BEFORE INSERT OR UPDATE ON &TABLEX FOR EACH ROW
DECLARE
DUMMY INTEGER;
BEGIN
IF :NEW.STATUS == 'ACTIVE' 
THEN
UPDATE &TABLEX SET STATUS = 'HISTORICAL'
WHERE 
STATION_NUMBER = :NEW.STATION_NUMBER
AND
STATUS = 'ACTIVE';
END IF;
END;
/
SET VERIFY ON
SET PAGESIZE 14