oracle初学者,请问oracle的触发器写法与SQL Server的有什么不同,最好能给些简单的触发器语句

解决方案 »

  1. 以下语句供参考:
    sql server中inserted指新插入,deleted指删除,而在oracle中:new代表当前新内容,:old代表旧内容。
    CREATE OR REPLACE TRIGGER TRG_TGL_STAMPOUT_MAIN
           BEFORE UPDATE OF UQCOMPANYID,OUTDATE ON TGL_STAMPOUT_MAIN FOR EACH ROW
    DECLARE
           V_UQSTAMPID   VARCHAR2(40);
           V_DENOM       NUMBER;
           V_INTCOUNT    NUMBER;
           V_OLDYEAR     NUMBER;
           V_OLDMONTH    NUMBER;
           V_OLDDAY      NUMBER;
           V_NEWYEAR     NUMBER;
           V_NEWMONTH    NUMBER;
           V_NEWDAY      NUMBER;
           V_COUNT       NUMBER;
           CURSOR V_CUR IS SELECT SD.UQSTAMPID,SD.DENOM,SD.INTCOUNT FROM TGL_STAMPOUT_DETAIL SD WHERE SD.UQSTAMPOUTID = :OLD.UQSTOREOUTID;
    BEGIN
           V_OLDYEAR   := TO_NUMBER( TO_CHAR( TO_DATE( :OLD.OUTDATE ,'YYYY_MM_DD'),'YYYY' ));
           V_OLDMONTH  := TO_NUMBER( TO_CHAR( TO_DATE( :OLD.OUTDATE ,'YYYY_MM_DD'),'MM' ));
           V_OLDDAY    := TO_NUMBER( TO_CHAR( TO_DATE( :OLD.OUTDATE ,'YYYY_MM_DD'),'DD' ));
           V_NEWYEAR   := TO_NUMBER( TO_CHAR( TO_DATE( :NEW.OUTDATE,'YYYY_MM_DD'),'YYYY' ));
           V_NEWMONTH  := TO_NUMBER( TO_CHAR( TO_DATE( :NEW.OUTDATE,'YYYY_MM_DD'),'MM' ));
           V_NEWDAY    := TO_NUMBER( TO_CHAR( TO_DATE( :NEW.OUTDATE,'YYYY_MM_DD'),'DD' ));       IF UPDATING('UQCOMPAYID') OR UPDATING('OUTDATE') THEN
              OPEN V_CUR;
              LOOP
                   FETCH V_CUR INTO V_UQSTAMPID,V_DENOM,V_INTCOUNT ;
                   EXIT WHEN V_CUR%NOTFOUND;               --减旧值
                   UPDATE TGL_STAMP_STORE
                     SET
                          OUTCOUNT = OUTCOUNT - V_INTCOUNT,
                          QMCOUNT = QMCOUNT + V_INTCOUNT
                   WHERE UQCOMPANYID = :OLD.UQCOMPANYID AND UQSTAMPID = V_UQSTAMPID AND INTYEAR = V_OLDYEAR AND INTMONTH = V_OLDMONTH;
                   DELETE FROM TGL_STAMP_STOREACCOUNT
                   WHERE UQCOMPANYID = :OLD.UQCOMPANYID AND UQSTAMPID = V_UQSTAMPID AND INTYEAR = V_OLDYEAR
                   AND INTMONTH = V_OLDMONTH AND INTDAY = V_OLDDAY AND INTDIRECTION = 1 AND UQSTOREOUTID = :OLD.UQSTOREOUTID;               --加新值
                   SELECT COUNT(*) INTO V_COUNT FROM TGL_STAMP_STORE
                   WHERE UQCOMPANYID = :NEW.UQCOMPANYID AND UQSTAMPID = V_UQSTAMPID AND INTYEAR = V_NEWYEAR AND INTMONTH = V_NEWMONTH;
                   IF V_COUNT = 0 THEN
                      INSERT INTO TGL_STAMP_STORE(UQCOMPANYID,UQSTAMPID,INTYEAR,INTMONTH,DENOM,QCCOUNT,QMCOUNT,INCOUNT,OUTCOUNT)
                      VALUES(:NEW.UQCOMPANYID,V_UQSTAMPID,V_NEWYEAR,V_NEWMONTH,V_DENOM,0,-V_INTCOUNT,0,V_INTCOUNT);
                   ELSE
                     UPDATE TGL_STAMP_STORE
                       SET
                            OUTCOUNT = OUTCOUNT + V_INTCOUNT,
                            QMCOUNT = QMCOUNT - V_INTCOUNT
                     WHERE UQCOMPANYID = :NEW.UQCOMPANYID AND UQSTAMPID = V_UQSTAMPID AND INTYEAR = V_NEWYEAR AND INTMONTH = V_NEWMONTH;
                   END IF;               INSERT INTO TGL_STAMP_STOREACCOUNT(UQCOMPANYID,UQSTAMPID,INTYEAR,INTMONTH,INTDAY,INTDIRECTION,INTCOUNT,DENOM,UQSTOREOUTID)
                   VALUES(:NEW.UQCOMPANYID,V_UQSTAMPID,V_NEWYEAR,V_NEWMONTH,V_NEWDAY,1,V_INTCOUNT,V_DENOM,:NEW.UQSTOREOUTID);
              END LOOP;
              CLOSE V_CUR;       END IF;
    END;
      

  2. 在begin...end之前,用declare定义变量。还有oracle特有的:new 和 :old
      

  3. 触发器类型:1、 语句触发器2、 行触发器3、INSTEAD OF触发4、 系统条件触发器5、 用户事件触发器2.1、语句级触发器.(语句级触发器对每个DML语句执行一次)是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。实例:createorreplacetriggertri_test afterinsertorupdateordeleteontest begin ifupdatingthen dbms_output.put_line(‘修改‘); elsifdeletingthen dbms_output.put_line(‘删除‘); elsifinsertingthen dbms_output.put_line(‘插入‘); endif; end;2.2、行级触发器.(行级触发器对DML语句影响的每个行执行一次)实例一:触发器 行级触发器
    createtabletest(sidnumber,snamevarchar2(20));--创建一个表 createsequenceseq_test;--创建序列 createorreplacetriggertri_test--创建触发器 beforeinsertorupdateofsidontest foreachrow--触发每一行 begin ifinsertingthen selectseq_test.nextvalinto:new.sidfromdual; else raise_application_error(-20020,‘不允许更新ID值!‘);--中断程序 endif; end;
    测试,插入几条记录
    insertintotestvalues(0,‘ff‘); insertintotestvalues(0,‘ff‘); insertintotestvalues(0,‘tt‘);实例二: 创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.
    createorreplacetriggertrig_job beforeinsertorupdateofjob onemp foreachrow begin ifinsertingthen :new.job:=upper(:new.job); else :new.job:=upper(:new.job); endif; end;2.3、instead of触发器.(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)语法如下:createorreplacetriggertrig_test insteadofinsertorupdateon表名 referencingnewasn foreachrow declare .......... begin ........ end;2.4、模式触发器.可以在模式级的操作上建立触发器.实例如下:createorreplacetriggerlog_drop_obj afterdroponschema begin insertinto..... end;
    2.5、数据库级触发器.可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.实例:createorreplacetriggertrig_name afterstartupondatabase begin ........... end;2.6、例子:需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。Createtablefoo(anumber); Createtriggerbiud_foo Beforeinsertorupdateordelete Onfoo Begin Ifusernotin(‘DONNY’)then Raise_application_error(-20001,‘Youdon’thaveaccesstomodifythistable.’); Endif; End; /即使SYS,SYSTEM用户也不能修改foo表2.7、[试验]对修改表的时间、人物进行日志记录。1、 建立试验表createtableemployees_copyasselect*fromhr.employees2、 建立日志表createtableemployees_log( whovarchar2(30), whendate);3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log表。Createorreplacetriggerbiud_employee_copy Beforeinsertorupdateordelete Onemployees_copy Begin Insertintoemployees_log(Who,when) Values(user,sysdate); End; /4、 测试updateemployees_copysetsalary=salary*1.1; select*fromemployess_log;5、 确定是哪个语句起作用?即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?可以在触发器中使用INSERTING / UPDATING / DELETING条件谓词,作判断:begin ifinsertingthen ----- elsifupdatingthen ----- elsifdeletingthen ------ endif; end; ifupdating(‘COL1’)orupdating(‘COL2’)then ------ endif;2.8、[试验]1、 修改日志表altertableemployees_log add(actionvarchar2(20));2、 修改触发器,以便记录语句类型。then l_action:=’Delete’; else raise_application_error(-20001,’Youshouldneverevergetthiserror.’); Insertintoemployees_log(Who,action,when) Values(user,l_action,sysdate); End;Createorreplacetriggerbiud_employee_copy Beforeinsertorupdateordelete Onemployees_copy Declare L_actionemployees_log.action%type; Begin ifinsertingthen l_action:=’Insert’; elsifupdatingthen l_action:=’Update’; elsifdeleting / 3、测试insertintoemployees_copy(employee_id,last_name,email,hire_date,job_id) values(12345,’Chen’,’[email protected]’,sysdate,12); select*fromemployees_log
      

  4. --头部文件CREATE OR REPLACE TRIGGER tri_create_code
    AFTER INSERT
    ON imx_file
    FOR EACH ROW
    DECLARE
     l_ima06 ima_file.ima06%type;
    --执行段
    BEGIN
           if inserting then
              IF :new.imx03 is NOT NULL THEN
                update ima_file set ima135='123456' where ima01=:new.imx000;
              END IF;
           end if;
          EXCEPTION
             WHEN others THEN
             null;
    END;
      

aliyun

类似问题 »