1
2
3
4
5
6
7
--使用管理员才可以创建系统触发器
CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE | AFTER] [DDL event | DATABASE event]  --系统触发事件
ON [DATABASE | SCHEMA]
[WHEN 触发条件] 
plsql_block

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE TRIGGER preserve_app_cols
   AFTER ALTER ON SCHEMA
DECLARE
   --获取一个表中所有列的游标
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = cp_owner AND table_name = cp_table;
BEGIN
   -- 如果正使用的是ALTER TABLE语句修改表
   IF ora_dict_obj_type = 'TABLE'
   THEN
      -- 循环表中的每一列
      FOR v_column_rec IN curs_get_columns (
                             ora_dict_obj_owner,
                             ora_dict_obj_name
                          )
      LOOP
         --判断当前的列名正在被修改
         IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN
            IF v_column_rec.column_name='EMPNO' THEN
               RAISE_APPLICATION_ERROR (-20003, '不能对empno字段进行修改');
            END IF; 
         END IF; 
         IF ORA_IS_DROP_COLUMN('EMPNO') THEN
            RAISE_APPLICATION_ERROR (-20004, '不能对empno字段进行删除');
         END IF; 
      END LOOP;
   END IF;
END;

1
2
3
4
5
6
7
8
9
10
11
12
--创建序列
CREATE SEQUENCE user_log_seq;
--创建日志表
CREATE TABLE user_log(
     logid        NUMBER          CONSTRAINT pk_logoid PRIMARY KEY,
     username     VARCHAR2(50)    NOT NULL,
     logondate    DATE,
     logoffdate   DATE,
     ip           VARCHAR2(20),
     logtype      VARCHAR2(20)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
	INSERT INTO user_log(logid, username, logondate, ip, logtype)
	VALUES(user_log_seq.nextval, ora_login_user, SYSDATE, ora_client_ip_address, 'LOGON');
END LOGON_TRIGGER;

--创建登出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
	INSERT INTO USER_LOG(LOGID, USERNAME, LOGOFFDATE, IP, LOGTYPE)
	VALUES(user_log_seq.nextval, ora_login_user, SYSDATE, ora_client_ip_address, 'LOGFF');
END LOGON_TRIGGER;

1
2
3
4
--切换用户进行登录

SELECT * FROM user_log;  --使用管理登录，查看user_log表

1
2
3
4
5
6
7
8
9
10
11
12
--创建序列
CREATE SEQUENCE db_event_log_seq;
--查询序列
SELECT * FROM user_sequences WHERE sequence_name = 'DB_EVENT_LOG_SEQ';
--创建数据库记录事件表
CREATE TABLE db_event_log(
     eventid      NUMBER          CONSTRAINT pk_eventid PRIMARY KEY,
     enentType    VARCHAR2(50)    NOT NULL,
     enentDate    DATE    		  NOT NULL,
     eventUser    VARCHAR2(50)    NOT NULL
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--创建启动之后触发器
CREATE OR REPLACE TRIGGER startup_trigger
AFTER STARTUP ON DATABASE    
BEGIN
	INSERT INTO db_event_log(eventid, eventtype, eventdate, eventuser)
	VALUES(db_event_log_seq.nextval, 'STARTUP', SYSDATE, ora_login_user);
	COMMIT;
END;

--创建关闭之前触发器
CREATE OR REPLACE TRIGGER shutdown_trigger
BEFORE SHUTDOWN ON DATABASE   
BEGIN
	INSERT INTO db_event_log(eventid, eventtype, eventdate, eventuser)
	VALUES(db_event_log_seq.nextval, 'SHUTDOWN', SYSDATE, ora_login_user);
	COMMIT;
END;

1
2
3
4
5
--测试在sqlplus中执行
SHUTDOWN ABORT; --关闭数据库
STARTUP;          --启动数据库
SELECT * FROM db_event_log;

1
2
3
4
5
6
7
8
9
10
11
12
13
--创建序列
CREATE SEQUENCE db_error_seq;
--查询序列
SELECT * FROM user_sequences WHERE sequence_name = 'DB_ERROR_SEQ';
---创建一张记录错误信息的数据表
CREATE TABLE db_error(
    eid    	    NUMBER    CONSTRAINT pk_eid PRIMARY KEY,
    username    VARCHAR2(50),
    errorDate   DATE,
    dbname      VARCHAR2(50),
    CONTENT     CLOB
);

1
2
3
4
5
6
7
8
9
10
--创建数据库错误触发器
CREATE OR REPLACE TRIGGER error_trigger
AFTER SERVEERROR ON DATABASE   
BEGIN
	INSERT INTO db_error(eid, username, errordate, dbname, content)
	VALUES(db_error_seq.nextval, ora_login_user, SYSDATE, ora_database_name, dbms_utility.format_error_stack);
     --dbms_utility包
     --dbms_utility.format_error_stack错误内容
END;