oracle创建dml触发器,Oracle数据库创建DML触发器

触发器的基本分类

1.行触发器：数据库表中的每一行有变化都会触发一次触发器代码

2.语句触发器：与语句所影响的行数无关，仅触发一次

3.BEFORE触发器：在DML语句执行之前触发

4.ALFTER触发器：在DML语句执行之后触发

DML触发器基本定义：

CREATE  [OR REPLACE] TRIGGER  [schema.] trigger

{BEFORE|AFTER}  verb_list ON [schema.]table

[FOR EACH ROW]

[WHEN (condition)]

BEGIN

.................

END;

创建一个行级触发器如下：

CREATE OR REPLACE TRIGGER TRIG_R_WIP_TRACKING_T

BEFORE UPDATE OR INSERT OR DELETE

ON SFCRUNTIME.R_WIP_TRACKING_T

FOR EACH ROW

BEGIN

IF INSERTING

THEN

INSERT INTO SFCRUNTIME.R_WO_BASE (ID,

WORKORDERNO,

WO_TYPE,

SKUNO,

SKU_VER,

SKU_NAME,

SKU_DESC)

VALUES ('002',

:new.mo_number,'normal',

:new.model_name,

:new.version_code,

:new.model_name,

:new.model_name);

ELSIF UPDATING

THEN

UPDATE R_WO_BASE

SET PLANT= :new.serial_number

WHERE WORKORDERNO= :new.mo_number;

ELSIF DELETING

THEN

DELETE FROM R_WO_BASE

WHERE WORKORDERNO=:old.mo_number;

END IF;

END;

注：执行上述触发器会报错--->[Error] ORA-01031 (10: 30): PL/SQL: ORA-01031: insufficient privileges

原因及解决：我登陆数据库用的是SYSTEM,而建立的触发器是基于SFCRUNTIME账户下的表，故会报无权限的错误

解决->创建触发器时触发器的名字前面也加上用户名前缀

最终正确触发器如下

CREATE OR REPLACE TRIGGER SFCRUNTIME.TRIG_R_WIP_TRACKING_T

BEFORE UPDATE OR INSERT OR DELETE

ON SFCRUNTIME.R_WIP_TRACKING_T

FOR EACH ROW

BEGIN

IF INSERTING

THEN

INSERT INTO SFCRUNTIME.R_WO_BASE (ID,

WORKORDERNO,

WO_TYPE,

SKUNO,

SKU_VER,

SKU_NAME,

SKU_DESC)

VALUES ('002',

:new.mo_number,'normal',

:new.model_name,

:new.version_code,

:new.model_name,

:new.model_name);

ELSIF UPDATING

THEN

UPDATE R_WO_BASE

SET PLANT= :new.serial_number

WHERE WORKORDERNO= :new.mo_number;

ELSIF DELETING

THEN

DELETE FROM R_WO_BASE

WHERE WORKORDERNO=:old.mo_number;

END IF;

END;

关于OLD和NEW谓词的几点说明

1.只有行触发器才可以使用OLD NEW谓词来获取语句执行前和执行后的记录

2.当在INSET语句上激发触发器时，OLD结构不包含任何值

3.UPDATE语句激发触发器，OLD结构包含之前旧的记录的值 NEW包含更新后的值

4.DELETE语句激发触发器，NEW不包含任何值，OLD包含已经被删除的值