Before INSERT OR INSERT Trigger

Trigger的名字叫做 SET_FLAG, 本來想先寫一個函數來檢查是不是在高速公路上. 查書後發現 SDO_GEOM.SDO_RELATE 配合 mask ANYINTERACT 就可以傳回TRUE or FALSE 然後直接改寫 FLAG 欄位

Trigger coses list:

create or replace

TRIGGER SET_FLAG

BEFORE INSERT OR UPDATE OF LOCATION ON CAR_TRACE_LOG

REFERENCING OLD AS old NEW AS new

FOR EACH ROW

BEGIN

select SDO_GEOM.RELATE(:NEW.location,'ANYINTERACT', H.WGS84, 0.005) into :NEW.FLAG from tw_highway H where H.AROAD_ID=999999;

END;