Oracle——触发器写法
Rehoni /
2020-09-01
Oracle触发器
create or replace trigger update_insert_trigger
after insert or update
on GEA_DM_FUNCTION_LOCATION
for each row
begin
if inserting then
if :NEW.RUNNING_STATE<>'1' then
DBMS_OUTPUT.PUT_LINE('是运行态=1,记录已经成功插入gea,执行插入clean');
insert into ELECTRANS.CLEAND_EMS_DEVICE(CLEAND_EMS_DEVICE.ID,
CLEAND_EMS_DEVICE.FL_NAME,
CLEAND_EMS_DEVICE.FL_CODE,
CLEAND_EMS_DEVICE.VINDICATE_OID,
SORT_ID,
CLEAND_EMS_DEVICE.BASE_VOLTAGE_ID,
CLEAND_EMS_DEVICE.LONGITUDE,
CLEAND_EMS_DEVICE.LATITUDE,
CLEAND_EMS_DEVICE.FULL_PATH,
RUNMANAG_OID)
values (:NEW.CLASSIFY_ID,
:NEW.FL_NAME,
:NEW.FL_CODE,
:NEW.VINDICATE_OID,
:NEW.CLASSIFY_ID,
:NEW.BASE_VOLTAGE_ID,
:NEW.LONGITUDE,
:NEW.LATITUDE,
:NEW.FULL_PATH,
:NEW.RUNMANAGE_OID);
else
DBMS_OUTPUT.PUT_LINE(:NEW.RUNNING_CODE);
DBMS_OUTPUT.PUT_LINE('不是运行态!=1,记录已经成功插入gea,不执行插入clean');
end if;
elsif updating then
if :NEW.RUNNING_STATE = 1 then
DBMS_OUTPUT.PUT_LINE('是运行态=1,记录已经成功插入gea,执行插入clean');
update ELECTRANS.CLEAND_EMS_DEVICE
set CLEAND_EMS_DEVICE.FL_NAME = :NEW.FL_NAME,
CLEAND_EMS_DEVICE.FL_CODE = :NEW.FL_CODE,
CLEAND_EMS_DEVICE.VINDICATE_OID = :NEW.VINDICATE_OID,
SORT_ID = :NEW.CLASSIFY_ID,
CLEAND_EMS_DEVICE.BASE_VOLTAGE_ID = :NEW.BASE_VOLTAGE_ID,
CLEAND_EMS_DEVICE.LONGITUDE = :NEW.LONGITUDE,
CLEAND_EMS_DEVICE.LATITUDE = :NEW.LATITUDE,
CLEAND_EMS_DEVICE.FULL_PATH = :NEW.FULL_PATH,
RUNMANAG_OID = :NEW.RUNMANAGE_OID
where CLEAND_EMS_DEVICE.ID = :NEW.CLASSIFY_ID;
else
DBMS_OUTPUT.PUT_LINE('不是运行态!=1,记录已经成功插入gea,不执行插入clean');
end if;
end if;
end;
select *
from ALL_TRIGGERS
where TABLE_NAME = 'GEA_DM_FUNCTION_LOCATION';