触发器的使用

 

1、定义

触发器：与表或数据库事件联系在一起，当一个触发器事件发生时，定义在表上的触发器被触发执行。

触发器触发次序

1）执行 BEFORE语句级触发器;

2）对与受语句影响的每一行：

· 执行 BEFORE行级触发器

· 执行 DML语句

· 执行 AFTER行级触发器

3）执行 AFTER语句级触发器

2、DML触发器

触发类型--行级和表级

行级：触发语句处理每一行时，行级别触发器都激发一次。

通过:old访问原始值、:new访问修改后的值，:old和:new只在触发器内部有效。

触发时间--之前和之后

建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去

建立删除日志表

CREATE TABLE emp_his

AS SELECT * FROM EMP1 WHERE 1=2;

创建触发器

CREATE OR REPLACE TRIGGER del_emp

BEFORE DELETE OR UPDATE ON scott.emp1 FOR  EACH ROW

BEGIN

INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

VALUES( :old.deptno, :old.empno, :old.ename , :old.job,

:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

3、INSTEAD-OF触发器：

INSTEAD OF选项使ORACLE激活触发器，而不执行触发事件

instead-of触发器只能定义在视图上，允许修改一个本来无法修改的视图。

可更改视图指：如果对视图的操作都是对基表的修改，并只有一个基表。

不可更改视图反之。

建立测试视图

CREATE OR REPLACE VIEW emp_view AS

SELECT deptno,COUNT(*) total_employee,SUM(sal) total_salary

FROM emp1 GROUP BY deptno;

--SELECT * FROM user_views;

对视图操作报错

DELETE FROM emp_view WHERE deptno=10;

建立替代触发器

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

DELETE FROM emp1 WHERE deptno=:old.deptno;

END;

再次执行DELETE

4、系统事件触发器

系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。
而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，
只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。

当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，
以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

使用SYS用户登录，做以下操作：

创建一个记录系统事件的表：

CREATE TABLE eventlog(

Eventname VARCHAR2(20) NOT NULL,

Eventdate date default sysdate,

Inst_num NUMBER NULL,

Db_name VARCHAR2(50) NULL,

Srv_error NUMBER NULL,

Username VARCHAR2(30) NULL,

Obj_type VARCHAR2(20) NULL,

Obj_name VARCHAR2(30) NULL,

Obj_owner VARCHAR2(30) NULL

)

创建触发器

BEGIN

-- 创建DDL触发器trig4_ddl

DBMS_UTILITY.EXEC_DDL_STATEMENT('

--由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

CREATE OR REPLACE TRIGGER trig4_ddl

AFTER CREATE OR ALTER OR DROP

ON DATABASE

DECLARE

Event VARCHAR2(20);

Typ VARCHAR2(20);

Name VARCHAR2(30);

Owner VARCHAR2(30);

BEGIN

-- 读取DDL事件属性

Event := SYSEVENT;

Typ := DICTIONARY_OBJ_TYPE;

Name := DICTIONARY_OBJ_NAME;

Owner := DICTIONARY_OBJ_OWNER;

-- 将事件属性插入到事件日志表中

INSERT INTO sys.eventlog(eventname, obj_type, obj_name, obj_owner)

VALUES(event, typ, name, owner);

END;

');

end;

/

-- 创建LOGON、STARTUP和SERVERERROR 事件触发器

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig4_after

AFTER LOGON OR STARTUP OR SERVERERROR

ON DATABASE

DECLARE

Event VARCHAR2(20);

Instance NUMBER;

Err_num NUMBER;

Dbname VARCHAR2(50);

User VARCHAR2(30);

BEGIN

Event := SYSEVENT;

IF event = ''LOGON'' THEN

User := LOGIN_USER;

INSERT INTO eventlog(eventname, username)

VALUES(event, user);

ELSIF event = ''SERVERERROR'' THEN

Err_num := SERVER_ERROR(1);

INSERT INTO eventlog(eventname, srv_error)

VALUES(event, err_num);

ELSE

Instance := INSTANCE_NUM;

Dbname := DATABASE_NAME;

INSERT INTO eventlog(eventname, inst_num, db_name)

VALUES(event, instance, dbname);

END IF;

END;

');

--创建LOGON事件触发器

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER LOGIN_HIS

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO LOGTABLE

VALUES(USER,SYSDATE);

END;

');

-- 创建LOGOFF和SHUTDOWN 事件触发器

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig4_before

BEFORE LOGOFF OR SHUTDOWN

ON DATABASE

DECLARE

Event VARCHAR2(20);

Instance NUMBER;

Dbname VARCHAR2(50);

User VARCHAR2(30);

BEGIN

Event := SYSEVENT;

IF event = ''LOGOFF'' THEN

User := LOGIN_USER;

INSERT INTO eventlog(eventname, username)

VALUES(event, user);

ELSE

Instance := INSTANCE_NUM;

Dbname := DATABASE_NAME;

INSERT INTO eventlog(eventname, inst_num, db_name)

VALUES(event, instance, dbname);

END IF;

END;

');

END;

查看eventlog表

SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error

FROM eventlog;

查看触发器

在数据字典USER_TRIGGERS中查看触发器

set long 200

SELECT trigger_name,(DESCRIPTION),trigger_body

FROM user_triggers

/

5、删除触发器

DROP TRIGGER trigger_name;

6、禁用启用

ALTER TIGGER trigger_name [DISABLE | ENABLE ];