Oracle中创建触发器示例及注意事项

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

DECLARE
pragma autonomous_transaction;
verror int;
BEGIN
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