oracle触发器有什么作用,Oracle中触发器的应用 (zt)

一、 DML触发器

DML触发器是指基于 DML操作所建立的触发器。在建立了DML触发器之后，如果执行了关于DML语句，那么Oracle会隐含的执行触发器代码。DML触发器包括语句触发器和行触发器两种类型。

1.1 语句触发器

语句触发器是指当执行DML语句时被隐含执行的触发器。在建立了语句触发器之后，如果执行了相关的DML语句，那么会执行该触发器的相应代码。注意，当执行语句触发器时，不能记录列数据的变化。建立语句触发器的语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name

{before | after } event1 [or event2 or event3]

ON table_name

如上所示。Trigger_name用于指定触发器的名称，event用于指定触发事件(INSERT、UPDATE、DELETE)，table_name用于指定DML语句所对应的表名。

1.建立BEFORE语句触发器

BEFORE语句触发器是指在执行DML语句之前被触发的触发器。下面以禁止在周六、周日改变EMP表数据为例，说明BEFORE语句触发器的方法。

CREATE OR REPLACE TRIGGER TR_SEC_EMP BEFORE

INSERT

OR UPDATE

OR DELETE ON EMPLOYEE

begin

if to_char(sysdate,'dy','nls_date_language=american')

in ('SAT','SUN') then

raise_application_error(-20001,'不能在休息日改变雇员信息');

end if;

end;

/

UPDATE employee sal =sal*1.1 WHERE deptno=4;

UPDATE employee sal=sal*1.1 WHERE deptno=4

ORA-20001: 不能在休息日改变雇员信息

ORA-06512: 在"TEST.TR_SEC_EMP1", line 2

ORA-04088: 触发器 'TEST.TR_SEC_EMP1' 执行过程中出错

2.建立AFTER语句触发器

AFTER语句触发器是指在执行了DML语句之后被触发的触发器。下面以用户对test表执行DML语句时，将相关信息记录到日志表为例，说明AFTER语句触发器的方法。

CREATE OR REPLACE TRIGGER test_trigger

AFTER DELETE OR INSERT OR UPDATE ON test

DECLARE

v_type test_log.l_type%TYPE;

BEGIN

IF INSERTING THEN

--INSERT触发

v_type := 'INSERT';

DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');

ELSE

IF UPDATING THEN --UPDATE触发 v_type := 'UPDATE';

DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');

ELSE

IF DELETING THEN v_type := 'DELETE';

DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');

END IF;

INSERT INTO test_log VALUES(user,v_type, TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));

END;

--下面我们来分别执行DML语句

INSERT INTO test VALUES(101,'zhao',22,'M');

UPDATE test SET t_age = 30 WHERE t_id = 101;

DELETE test WHERE t_id = 101;

--然后查看效果

SELECT * FROM test;SELECT * FROM test_log;

3.使用条件谓词

当DML触发器包含多个触发事件(INSERT、UPDATE、DELETE)时，为了区分具体的触发事件，可以使用以下谓词：

INSERTING：当触发事件是INSERT语句时，返回TRUE，否则返回FALSE。

UPDATING：当触发事件是UPDATE语句时，返回TRUE，否则返回FALSE。

DELETING：当触发事件是DELETE语句时，返回TRUE，否则返回FALSE。

下面以防止周六、周日在EMPLOYEE表上执行DML，根据不同谓词显示不同错误信息，并建立tr_sec_emp为例，说明使用条件谓词的方法。

create or replace trigger tr_sec_emp

before insert or update or delete on employee

begin

if to_char(sysdate,'dy','nls_data_language')

in ('SAT','SUN') then

case

when inserting then

raise_application_error(-20001,'不能在休息日执行INSERT');

when updating then

raise_application_error(-20002,'不能在休息日执行UPDATE');

when deleting then

raise_application_error(-20003,'不能在休息日执行DELETE');

end case;

end if;

end;

/

执行DML语句

Delete from employee where empno=4;

1.2 行触发器

行触发器是指当执行DML语句时，每作用一行被触发一次的触发器。当使用DML语句触发器时，不能记录数据的变化；为了审计表数据的变化，就需要使用行触发器。注意，当编写行触发器时，触发器代码不能从触发器所对应的基表中读取数据。建立行触发器的语法如下:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER} event1 [OR event2 OR event3]

ON table_name [REFERENCING OLD AS old | NEW AS new]

FOR EACH ROW [WHEN condition]

如上所示,trigger_name用于指定触发器名,event用于指定触发事件(INSERT、UPDATE、DELETE)，table_name用于指定DML语句所对应的表名，REFERENCING子句用于指定引用新、旧数据的方式，默认情况下使用old操作符引用旧数据，使用new操作符引用新数据，FOR EACH ROW用于指定建立行触发器，WHEN子句(可选)用于指定触发事件。

1.建立BEFORE行触发器

BEFORE行触发器是指在处理行之前被触发的触发器。下面以确保雇员工资不能低于其原有工资，并建立行触发器tr_emp_sal为例，说明建立BEFORE行触发器的方法。

create or replace trigger tr_emp_sal

before update of sal on employee for each row

begin

if :new.salraise_application_error(-20010,'工资只升不降');

end if;

end;

/

update employee set sal=1000 where empno=10

ORA-20931: 工资只升不降,并且升幅不能超过20%

ORA-06512: 在"TEST.TR_CHECK_SAL", line 2

ORA-04088: 触发器 'TEST.TR_CHECK_SAL' 执行过程中出错

2.建立AFTER行触发器

AFTER行触发器是指在处理行之后被触发的触发器。下面以修改雇员表里的部门号必先修改部门表的部门号，并建立行触发器tr_update_deptno为例，说明建立AFTER行触发器的方法。

create or replace trigger tr_update_tr_update_deptno

after update of deptno on department for each row

begin

update employee set deptno=:new.deptno where deptno=:old.deptno;

end;

/

update department set deptno=8 where deptno=5;

select ename from employee where deptno=8;

ENAME

----------

CLARK

KING

MILLER

关键字：DML 语句触发器 行触发器 BEFORE AFTER 条件谓词