Oracle - 把触发器看透

INSERT INTO myview VALUES (70, 'demo', 'D'); INSERT INTO myview VALUES (9999, USER, 'E');

SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70; DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name); SELECT empno, ename INTO no, name FROM emp WHERE empno=9999; DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name); DELETE FROM emp WHERE empno=9999; DELETE FROM dept WHERE deptno=70;

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3'); END;

例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。

BEGIN

-- 创建用于记录事件日志的数据表

DBMS_UTILITY.EXEC_DDL_STATEMENT(' 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 ) ');

-- 创建DDL触发器trig4_ddl

DBMS_UTILITY.EXEC_DDL_STATEMENT(' 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 scott.eventlog(eventname, obj_type, obj_name, obj_owner)

VALUES(event, typ, name, owner); 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; ');

-- 创建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;

CREATE TABLE mydata(mydate NUMBER); CONNECT SCOTT/TIGER

COL eventname FORMAT A10 COL eventdate FORMAT A12 COL username FORMAT A10 COL obj_type FORMAT A15 COL obj_name FORMAT A15 COL obj_owner FORMAT A10

SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error FROM eventlog;

DROP TRIGGER trig4_ddl; DROP TRIGGER trig4_before; DROP TRIGGER trig4_after; DROP TABLE eventlog; DROP TABLE mydata;

8.6 数据库触发器的应用实例

用户可以使用数据库触发器实现各种功能：

? 复杂的审计功能；

例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。

CREATE TABLE audit_table( Audit_id NUMBER, User_name VARCHAR2(20), Now_time DATE,

Terminal_name VARCHAR2(10), Table_name VARCHAR2(10), Action_name VARCHAR2(10), Emp_id NUMBER(4));

CREATE TABLE audit_table_val( Audit_id NUMBER,

Column_name VARCHAR2(10), Old_val NUMBER(7,2), New_val NUMBER(7,2));

CREATE SEQUENCE audit_seq START WITH 1000 INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp

AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE

Time_now DATE; Terminal CHAR(10); BEGIN

Time_now:=sysdate;

Terminal:=USERENV('TERMINAL'); IF INSERTING THEN

INSERT INTO audit_table

VALUES(audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'INSERT', :new.empno); ELSIF DELETING THEN

INSERT INTO audit_table

VALUES(audit_seq.NEXTVAL, user, time_now,