|
AR2(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 数据库触发器的应用实例
用户可以使用数据库触发器实现各种功能：
l 复杂的审计功能；
例：将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,
terminal, 'EMP', 'DELETE', :ol |