在trigger中实现本表中修改插入的行

做一个表的insert的trigger，目的是只修改插入行的字段。

 

CREATE OR REPLACE TRIGGER TR_RME_SLOT BEFORE

    INSERT ON RME_SLOT

    FOR EACH ROW

BEGIN

  IF (:NEW. POSITION >= 0 AND :NEW. POSITION < 10 ) THEN

      :NEW. SLOT_NAME := '0' ||TO_CHAR (:NEW. POSITION );

  ELSE

      :NEW. SLOT_NAME := TO_CHAR (:NEW. POSITION );

  END IF;

END;

 

在插入以前就需要修改插入行；在trigger实现中并不需要用到update语句

 

同时，如果要在trigger中实现对本表记录的修改，则需要这样写：

 

CREATE OR REPLACE TRIGGER TR_RME_SLOT AFTER

    INSERT ON RME_SLOT

    FOR EACH ROW

DECLARE

   PRAGMA AUTONOMOUS_TRANSACTION ;

BEGIN

  IF (:NEW. POSITION >= 0 AND :NEW. POSITION < 10 ) THEN

      UPDATE RME_SLOT   SET SLOT_NAME = '0' ||TO_CHAR (:NEW. POSITION ) WHERE SLOT_ID =:NEW. SLOT_ID ;

  ELSE

      UPDATE RME_SLOT   SET SLOT_NAME = TO_CHAR (:NEW. POSITION ) WHERE SLOT_ID =:NEW. SLOT_ID ;

  END IF;

  COMMIT;

END;

 

注意多了一段DECLARE，同时在trigger结束时需要COMMIT

本人经过一天的苦战，修改后成功执行一触发器：

create or replace trigger tri_ZSY_LEAVETABLE  
          before   insert     ON   "ZSY_LEAVETABLE"   FOR   EACH   ROW  

declare cctype number ; 
          begin
               select posi_type into cctype  from base_gov.a_frameuser
               where user_code=:new.personname;
               :new.persontype:=cctype;
                 
          end;