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,