--头部文件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;
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;
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’，’Donny@hotmail’，sysdate，12); select*fromemployees_log
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;