1、oracle 中建立觸發器示例
CREATE TABLE "CONCEPT"."FREQUENCYMODIFYLOG"
( "FREQUENCYID" NUMBER(10,0),
"NAME" NVARCHAR2(30),
"CODE" VARCHAR2(10 CHAR),
"MNEMONICCODE" VARCHAR2(10 CHAR),
"SPELLCODE" VARCHAR2(10 CHAR),
"WBCODE" VARCHAR2(10 CHAR),
"ENGLISHNAME" NVARCHAR2(30),
"TIMESDAILY" NUMBER(5,0) DEFAULT (0),
"INTERVALDAYS" NUMBER(5,0) DEFAULT (0),
"ISWEEKLYCYCLE" NUMBER(1,0) DEFAULT (0),
"NOTE" NVARCHAR2(100),
"ISDELETED" NUMBER(1,0) DEFAULT (0),
"ROWVERSION" DATE DEFAULT SYSDATE,
"WEEKDAYSERIES" VARCHAR2(100 CHAR),
"STANDARDEXECTIMESERIES" VARCHAR2(255 CHAR),
"NONSTANDARDEXECTIMESERIES" VARCHAR2(255 CHAR),
"ENGLISHNOTE" NVARCHAR2(100),
"ORDINAL" NUMBER(10,0) DEFAULT (0),
"N_NAME" NVARCHAR2(30),
"N_CODE" VARCHAR2(10 CHAR),
"N_MNEMONICCODE" VARCHAR2(10 CHAR),
"N_SPELLCODE" VARCHAR2(10 CHAR),
"N_WBCODE" VARCHAR2(10 CHAR),
"N_ENGLISHNAME" NVARCHAR2(30),
"N_TIMESDAILY" NUMBER(5,0) DEFAULT (0),
"N_INTERVALDAYS" NUMBER(5,0) DEFAULT (0),
"N_ISWEEKLYCYCLE" NUMBER(1,0) DEFAULT (0),
"N_NOTE" NVARCHAR2(100),
"N_ISDELETED" NUMBER(1,0) DEFAULT (0),
"N_ROWVERSION" DATE DEFAULT SYSDATE,
"N_WEEKDAYSERIES" VARCHAR2(100 CHAR),
"N_STANDARDEXECTIMESERIES" VARCHAR2(255 CHAR),
"N_NONSTANDARDEXECTIMESERIES" VARCHAR2(255 CHAR),
"N_ENGLISHNOTE" NVARCHAR2(100),
"N_ORDINAL" NUMBER(10,0) DEFAULT (0),
"USERNAME" VARCHAR2(30) NOT NULL,
"IP" VARCHAR2(30) NOT NULL
) ;
/
CREATE OR replace TRIGGER concept.trg_updfrequency before UPDATE ON concept.frequency FOR each row
DECLARE
v_ip varchar2(30);
v_user varchar2(30);
BEGIN
SELECT sys_context('userenv', 'ip_address'), sys_context('userenv', 'SESSION_USER') INTO v_ip, v_user FROM dual;
--if :new.DISCHARGEON is null then
INSERT INTO "CONCEPT"."FREQUENCYMODIFYLOG" (FREQUENCYID, NAME, CODE, MNEMONICCODE, SPELLCODE, WBCODE, ENGLISHNAME, TIMESDAILY, INTERVALDAYS, ISWEEKLYCYCLE, NOTE, ISDELETED, ROWVERSION, WEEKDAYSERIES, STANDARDEXECTIMESERIES, NONSTANDARDEXECTIMESERIES, ENGLISHNOTE, ORDINAL
, N_NAME, N_CODE, N_MNEMONICCODE, N_SPELLCODE, N_WBCODE, N_ENGLISHNAME, N_TIMESDAILY, N_INTERVALDAYS, N_ISWEEKLYCYCLE, N_NOTE, N_ISDELETED, N_ROWVERSION, N_WEEKDAYSERIES, N_STANDARDEXECTIMESERIES, N_NONSTANDARDEXECTIMESERIES, N_ENGLISHNOTE, N_ORDINAL
, USERNAME, IP)
VALUES( :old.FREQUENCYID, :old.NAME, :old.CODE, :old.MNEMONICCODE, :old.SPELLCODE, :old.WBCODE, :old.ENGLISHNAME, :old.TIMESDAILY, :old.INTERVALDAYS, :old.ISWEEKLYCYCLE, :old.NOTE, :old.ISDELETED, :old.ROWVERSION, :old.WEEKDAYSERIES, :old.STANDARDEXECTIMESERIES, :old.NONSTANDARDEXECTIMESERIES, :old.ENGLISHNOTE, :old.ORDINAL,
:new.NAME, :new.CODE, :new.MNEMONICCODE, :new.SPELLCODE, :new.WBCODE, :new.ENGLISHNAME, :new.TIMESDAILY, :new.INTERVALDAYS, :new.ISWEEKLYCYCLE, :new.NOTE, :new.ISDELETED, :new.ROWVERSION, :new.WEEKDAYSERIES, :new.STANDARDEXECTIMESERIES, :new.NONSTANDARDEXECTIMESERIES, :new.ENGLISHNOTE, :new.ORDINAL, v_user, v_ip);
--end if;
END trg_updfrequency;
2、oracle 中觸發器增加存儲過程commit問題
觸發器無需commit
也不能寫commit
觸發器和觸發它的DML是同一個事務
DML送出了，觸發器的操作也送出了，要不就一起復原了
當然，如果你一定要在觸發器裡寫COMMIT
那就用自治事務
相當于一個事務裡的子事務
正常情況下，Oracle規定在觸發器中不能運作 ddl語句和commit，rollback語句。
解決辦法有兩種：
1.在可以在觸發器中加入：pragma autonomous_transaction; 表示自由事務處理。
如：
create or replace trigger UPDATE_relaction_SAMPLE
before update ON SAMPLE
REFERENCING
FOR EACH ROW
pragma autonomous_transaction;
verror int;
verror:=0;
update sample_relation t set t.status=:new.status where
t.sample_id=:new.trim_idnumeric;
if :new.status='C' and :old.status<>'C' then
proc_synch_procedure_data(:new.trim_idnumeric,verror);
end if;
commit;
END UPDATE_relaction_SAMPLE;
2.可以另外寫一個方法，把dll語句傳遞到這個方法中去執行。
注釋：
ddl語句：DDL語句用語定義和管理資料庫中的對象，如Create,Alter，Drop，truncate等；DDL操作是隐性送出的！操作立即生效,原資料不放到rollback segment中,不能復原. 操作不觸發trigger
DML(Data Manipulation Language)資料操縱語言指令使使用者能夠查詢資料庫以及操作已有資料庫中的資料。如insert,delete,update,select等都是DML