Luo Hao

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';