Oracle触发器常见实例

最新推荐文章于 2023-05-16 23:38:37 发布

Lissen_Gan

最新推荐文章于 2023-05-16 23:38:37 发布

阅读量851

点赞数 1

分类专栏： Oracle 文章标签： plsql 触发器数据库 oracle sql

本文链接：https://blog.csdn.net/u011285708/article/details/103947348

版权

Oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

1、创建随数据库启动时自动启动PDB数据库（系统触发器）

create or replace trigger trigger_sys_open_pdb01
        after startup on database
begin
        execute immediate 'alter pluggable database pdb01 open';
end;
/

2、创建限制修改表中数据的日期触发器（DML语句级触发器）

创建测试表T1
SCOTT@TNS_PDB01>create table t1(id number,name varchar2(20));

Table created.

SCOTT@TNS_PDB01>insert into t1 values(1,'a');

1 row created.

SCOTT@TNS_PDB01>insert into t1 values(2,'b');

1 row created.

SCOTT@TNS_PDB01>insert into t1 values(3,'c');

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>select * from t1;

 ID NAME
--- ---------------
  1 a
  2 b
  3 c

**创建DML语句级触发器用来限制修改表的时间（周末不能修改）**
create or replace trigger tri_dml_statement_t1
        before insert or delete or update on t1
declare
        lv_curr_day varchar2(10);
begin
        select to_char(sysdate,'d') into lv_curr_day from dual;
        if lv_curr_day in ('1','7') then
                raise_application_error('-20001','can''t modify data on table t1 in weekday.');
        end if;
end tri_dml_statement_t1;
/

修改系统时间方便测试
[root@kkk ~]# date -s '2020-01-12 21:58:43'

测试TRIGGER
SCOTT@TNS_PDB01>select * from t1;

 ID NAME
--- ---------------
  1 a
  2 b
  3 c

SCOTT@TNS_PDB01>delete from t1 where id = 1;
delete from t1 where id = 1
            *
ERROR at line 1:
ORA-20001: can't modify data on table t1 in weekday.
ORA-06512: at "SCOTT.TRI_DML_STATEMENT_T1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI_DML_STATEMENT_T1'


SCOTT@TNS_PDB01>update t1 set name = name || 1;
update t1 set name = name || 1
       *
ERROR at line 1:
ORA-20001: can't modify data on table t1 in weekday.
ORA-06512: at "SCOTT.TRI_DML_STATEMENT_T1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI_DML_STATEMENT_T1'


SCOTT@TNS_PDB01>insert into t1 values(4,'d');
insert into t1 values(4,'d')
            *
ERROR at line 1:
ORA-20001: can't modify data on table t1 in weekday.
ORA-06512: at "SCOTT.TRI_DML_STATEMENT_T1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRI_DML_STATEMENT_T1'

**查看TRIGGER TRI_DML_STATEMENT_T1元数据**
SCOTT@TNS_PDB01>column TRI_DML_STATEMENT_T1 format a111;
SCOTT@TNS_PDB01>select dbms_metadata.get_ddl(object_type => 'TRIGGER',name => 'TRI_DML_STATEMENT_T1',schema => 'SCOTT') as TRI_DML_STATEMENT_T1 from dual;

TRI_DML_STATEMENT_T1
-----------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TRIGGER "SCOTT"."TRI_DML_STATEMENT_T1"
	before insert or delete or update on t1
declare
	lv_curr_day varchar2(10);
begin
	select to_char(sysdate,'d') into lv_curr_day from dual;
	if lv_curr_day in ('1','7') then
		raise_application_error('-20001','can''t modify data on table
 t1 in weekday.');
	end if;
end tri_dml_statement_t1;
ALTER TRIGGER "SCOTT"."TRI_DML_STATEMENT_T1" ENABLE

3、创建触发器实现插入表中ID自增（DML行级触发器）

创建测试表和SEQUENCE SEQ_T1
SCOTT@TNS_PDB01>create table t1(id number,name varchar2(10),hiredate date);

Table created.

SCOTT@TNS_PDB01>create sequence seq_t1;

Sequence created.

创建TRIGGER
create or replace trigger tri_dml_row_t1
        before insert on t1
        for each row
declare
        lv_id number;
begin
        select seq_t1.nextval into lv_id from dual;
        :new.id := lv_id;
end tri_dml_row_t1;
/

测试TRIGGER
SCOTT@TNS_PDB01>select * from t1;

no rows selected

SCOTT@TNS_PDB01>insert into t1(name,hiredate) values('gan',to_date('2001-01-01 20:05:03','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SCOTT@TNS_PDB01>insert into t1(name,hiredate) values('xin',to_date('2021-01-01 20:05:03','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>select * from t1;

 ID NAME	    HIREDATE
--- --------------- -------------------
  1 gan 	    2001-01-01 20:05:03
  2 xin 	    2021-01-01 20:05:03

4、创建触发器来记录表的修改（DML语句级触发器）

创建测试表
SCOTT@TNS_PDB01>create table t1(t_id number,t_name varchar2(20),t_age number,t_sex char(1));

Table created.

SCOTT@TNS_PDB01>desc t1;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 T_ID								      NUMBER
 T_NAME 							      VARCHAR2(20)
 T_AGE								      NUMBER
 T_SEX								      CHAR(1)

SCOTT@TNS_PDB01>create table t1_log(l_user varchar2(15),l_type varchar2(15),l_date date);

Table created.

SCOTT@TNS_PDB01>desc t1_log;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 L_USER 							      VARCHAR2(15)
 L_TYPE 							      VARCHAR2(15)
 L_DATE 							      DATE

创建TRIGGER TRI_DML_STATEMENT_T1
create or replace trigger tri_dml_statement_t1
        after insert or delete or update on t1
declare
        lv_type t1_log.l_type%type;
begin
        if INSERTING then
                lv_type := 'INSERT';
                dbms_output.put_line('Inserted successful,Record has been inserted into t1_log.');
        elsif UPDATING then
                lv_type := 'UPDATE';
                dbms_output.put_line('Updated successful,Record has been inserted into t1_log.');
        elsif DELETING then
                lv_type := 'DELETE';
                dbms_output.put_line('Deleted successful,Record has been inserted into t1_log.');
        end if;
        insert into t1_log values(user,lv_type,sysdate);
end tri_dml_statement_t1;
/

查看TRIGGER TRI_DML_STATEMENT_T1相关信息

查看TRIGGER TRI_DML_STATEMENT_T1元数据
SCOTT@TNS_PDB01>select dbms_metadata.get_ddl(object_type => 'TRIGGER',name => 'TRI_DML_STATEMENT_T1',schema => 'SCOTT') as TRI_DML_STATEMENT_T1 from dual;

TRI_DML_STATEMENT_T1
-----------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE TRIGGER "SCOTT"."TRI_DML_STATEMENT_T1"
	after insert or delete or update on t1
declare
	lv_type t1_log.l_type%type;
begin
	if INSERTING then
		lv_type := 'INSERT';
		dbms_output.put_line('Inserted successful,Record has been inserted int
o t1_log.');
	elsif UPDATING then
		lv_type := 'UPDATE';
		dbms_output.put_line('Updated successful,Record has been inserted into t1_lo
g.');
	elsif DELETING then
		lv_type := 'DELETE';
		dbms_output.put_line('Deleted successful,Record has been
 inserted into t1_log.');
	end if;
	insert into t1_log values(user,lv_type,sysdate);
end tri_dml_statement_t1;
ALTER TRIGGER "SCOTT"."TRI_DML_STATEMENT_T1" ENABLE

SCOTT@TNS_PDB01>select owner,trigger_name,trigger_type,status from dba_triggers where trigger_name = 'TRI_DML_STATEMENT_T1';

OWNER	   TRIGGER_NAME 	TRIGGER_TYPE	 STATUS
---------- -------------------- ---------------- ----------
SCOTT	   TRI_DML_STATEMENT_T1 AFTER STATEMENT  ENABLED

SCOTT@TNS_PDB01>alter trigger TRI_DML_STATEMENT_T1 disable;

Trigger altered.

SCOTT@TNS_PDB01>select owner,trigger_name,trigger_type,status from dba_triggers where trigger_name = 'TRI_DML_STATEMENT_T1';

OWNER	   TRIGGER_NAME 	TRIGGER_TYPE	 STATUS
---------- -------------------- ---------------- ----------
SCOTT	   TRI_DML_STATEMENT_T1 AFTER STATEMENT  DISABLED

SCOTT@TNS_PDB01>alter trigger TRI_DML_STATEMENT_T1 compile;

Trigger altered.

SCOTT@TNS_PDB01>select owner,trigger_name,trigger_type,status from dba_triggers where trigger_name = 'TRI_DML_STATEMENT_T1';

OWNER	   TRIGGER_NAME 	TRIGGER_TYPE	 STATUS
---------- -------------------- ---------------- ----------
SCOTT	   TRI_DML_STATEMENT_T1 AFTER STATEMENT  DISABLED

SCOTT@TNS_PDB01>alter trigger TRI_DML_STATEMENT_T1 enable;

Trigger altered.

SCOTT@TNS_PDB01>select owner,trigger_name,trigger_type,status from dba_triggers where trigger_name = 'TRI_DML_STATEMENT_T1';

OWNER	   TRIGGER_NAME 	TRIGGER_TYPE	 STATUS
---------- -------------------- ---------------- ----------
SCOTT	   TRI_DML_STATEMENT_T1 AFTER STATEMENT  ENABLED

测试TRIGGER
SCOTT@TNS_PDB01>select * from t1;

no rows selected

SCOTT@TNS_PDB01>select * from t1_log;

no rows selected

SCOTT@TNS_PDB01>desc t1;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 T_ID								      NUMBER
 T_NAME 							      VARCHAR2(20)
 T_AGE								      NUMBER
 T_SEX								      CHAR(1)

SCOTT@TNS_PDB01>insert into t1 values(1,'xiaoming',12,'F');
Inserted successful,Record has been inserted into t1_log.

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>select * from t1;

      T_ID T_NAME		     T_AGE T
---------- -------------------- ---------- -
	 1 xiaoming			12 F

SCOTT@TNS_PDB01>select * from t1_log;

L_USER		L_TYPE		L_DATE
--------------- --------------- -------------------
SCOTT		INSERT		2020-01-09 23:20:03

SCOTT@TNS_PDB01>insert into t1 values(2,'xiaohong',13,'M');
Inserted successful,Record has been inserted into t1_log.

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>delete from t1 where t_id = 1;
Deleted successful,Record has been inserted into t1_log.

1 row deleted.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>select * from t1;

      T_ID T_NAME		     T_AGE T
---------- -------------------- ---------- -
	 2 xiaohong			13 M

SCOTT@TNS_PDB01>select * from t1_log;

L_USER		L_TYPE		L_DATE
--------------- --------------- -------------------
SCOTT		INSERT		2020-01-09 23:20:03
SCOTT		INSERT		2020-01-09 23:20:44
SCOTT		DELETE		2020-01-09 23:21:08

SCOTT@TNS_PDB01>update t1 set t_name = 'xiaozhang';
Updated successful,Record has been inserted into t1_log.

1 row updated.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>select * from t1;

      T_ID T_NAME		     T_AGE T
---------- -------------------- ---------- -
	 2 xiaozhang			13 M

SCOTT@TNS_PDB01>select * from t1_log;

L_USER		L_TYPE		L_DATE
--------------- --------------- -------------------
SCOTT		INSERT		2020-01-09 23:20:03
SCOTT		INSERT		2020-01-09 23:20:44
SCOTT		DELETE		2020-01-09 23:21:08
SCOTT		UPDATE		2020-01-09 23:21:58

5、创建触发器，它将映射EMP_TEMP表中每个部门的总人数和总工资（DML语句级触发器）

创建测试表
SCOTT@TNS_PDB01>create table emp_temp as select * from emp;

Table created.

SCOTT@TNS_PDB01>create table dept_sal as select deptno,count(empno) total_empno,sum(sal) total_sal from emp_temp group by deptno;

Table created.

SCOTT@TNS_PDB01>desc dept_sal;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 DEPTNO 							      NUMBER(2)
 TOTAL_EMPNO							      NUMBER
 TOTAL_SAL							      NUMBER

SCOTT@TNS_PDB01>select * from dept_sal;

    DEPTNO TOTAL_EMPNO	TOTAL_SAL
---------- ----------- ----------
	30	     6	     9400
	10	     3	     8750
	20	     5	    10875

创建TRIGGER
create or replace trigger tri_dml_statement_emp_temp
        after insert or delete or update on emp_temp
declare
        cursor cv_emp_temp is
                select deptno,count(empno) total_empno,sum(sal) total_sal from emp_temp group by deptno;
begin
		--触发触发器前先清空DEPT_SAL表
        delete from dept_sal;
        for i in cv_emp_temp loop
                insert into dept_sal values(i.deptno,i.total_empno,i.total_sal);
        end loop;
end tri_dml_statement_emp_temp;
/

测试TRIGGER
SCOTT@TNS_PDB01>select * from dept_sal;

    DEPTNO TOTAL_EMPNO	TOTAL_SAL
---------- ----------- ----------
	30	     6	     9400
	10	     3	     8750
	20	     5	    10875

SCOTT@TNS_PDB01>delete from emp_temp where empno = 7369;

1 row deleted.

SCOTT@TNS_PDB01>select * from dept_sal;

    DEPTNO TOTAL_EMPNO	TOTAL_SAL
---------- ----------- ----------
	30	     6	     9400
	10	     3	     8750
	20	     4	    10075

SCOTT@TNS_PDB01>delete from emp_temp where empno > 7500;

12 rows deleted.

SCOTT@TNS_PDB01>select * from dept_sal;

    DEPTNO TOTAL_EMPNO	TOTAL_SAL
---------- ----------- ----------
	30	     1	     1600

SCOTT@TNS_PDB01>update emp_temp set sal = 8000 where empno = 7499;

1 row updated.

SCOTT@TNS_PDB01>select * from dept_sal;

    DEPTNO TOTAL_EMPNO	TOTAL_SAL
---------- ----------- ----------
	30	     1	     8000

6、创建触发器，用来记录表的删除数据（DML行级触发器）

创建测试表
SCOTT@TNS_PDB01>create table t1 as select empno,ename,sal from emp;

Table created.

SCOTT@TNS_PDB01>select * from t1;

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800
      7499 ALLEN	    1600
      7521 WARD 	    1250
      7566 JONES	    2975
      7654 MARTIN	    1250
      7698 BLAKE	    2850
      7782 CLARK	    2450
      7788 SCOTT	    3000
      7839 KING 	    5000
      7844 TURNER	    1500
      7876 ADAMS	    1100
      7900 JAMES	     950
      7902 FORD 	    3000
      7934 MILLER	    1300

14 rows selected.

SCOTT@TNS_PDB01>create table t1_old as select * from t1 where 1 = 0;

Table created.

SCOTT@TNS_PDB01>select * from t1_old;

no rows selected

创建TRIGGER
create or replace trigger tri_dml_row_t1
        after delete on t1
        for each row				--影响每一行	
begin
        insert into t1_old values(:old.empno,:old.ename,:old.sal);
end tri_dml_row_t1;
/

测试TRIGGER
SCOTT@TNS_PDB01>select * from t1;

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800
      7499 ALLEN	    1600
      7521 WARD 	    1250
      7566 JONES	    2975
      7654 MARTIN	    1250
      7698 BLAKE	    2850
      7782 CLARK	    2450
      7788 SCOTT	    3000
      7839 KING 	    5000
      7844 TURNER	    1500
      7876 ADAMS	    1100
      7900 JAMES	     950
      7902 FORD 	    3000
      7934 MILLER	    1300

14 rows selected.

SCOTT@TNS_PDB01>select * from t1_old;

no rows selected

SCOTT@TNS_PDB01>delete from t1 where sal between 1000 and 1500;

5 rows deleted.

SCOTT@TNS_PDB01>select * from t1;

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800
      7499 ALLEN	    1600
      7566 JONES	    2975
      7698 BLAKE	    2850
      7782 CLARK	    2450
      7788 SCOTT	    3000
      7839 KING 	    5000
      7900 JAMES	     950
      7902 FORD 	    3000

9 rows selected.

SCOTT@TNS_PDB01>select * from t1_old;

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7521 WARD 	    1250
      7654 MARTIN	    1250
      7844 TURNER	    1500
      7876 ADAMS	    1100
      7934 MILLER	    1300

7、创建触发器，利用视图插入数据（INSTEAD OF触发器）

创建测试所使用的表
SCOTT@TNS_PDB01>create table t1(id number primary key,name varchar2(20),age number);

Table created.

SCOTT@TNS_PDB01>create table t2(id number,tel varchar2(15),addr varchar2(30));

Table created.

SCOTT@TNS_PDB01>desc t1;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 ID							     NOT NULL NUMBER
 NAME								      VARCHAR2(20)
 AGE								      NUMBER

SCOTT@TNS_PDB01>desc t2;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 ID								      NUMBER
 TEL								      VARCHAR2(15)
 ADDR								      VARCHAR2(30)

SCOTT@TNS_PDB01>insert into t1 values(101,'xiaohong',12);

1 row created.

SCOTT@TNS_PDB01>insert into t1 values(102,'xiaoming',13);

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

SCOTT@TNS_PDB01>insert into t2 values(101,'123456','aaaaaaaaaaa');

1 row created.

SCOTT@TNS_PDB01>insert into t2 values(102,'456789','bbbbbbbb');

1 row created.

SCOTT@TNS_PDB01>commit;

Commit complete.

创建用于插入数据的视图VIEW_T1_T2
create or replace view view_t1_t2 as select t1.id,t1.name,t2.tel,t2.addr from t1,t2 where t1.id = t2.id;

View created.

SCOTT@TNS_PDB01>select * from t1;

   ID NAME		     AGE
----- --------------- ----------
  101 xiaohong		      12
  102 xiaoming		      13

SCOTT@TNS_PDB01>select * from t2;

   ID TEL	      ADDR
----- --------------- ------------------------------
  101 123456	      aaaaaaaaaaa
  102 456789	      bbbbbbbb

SCOTT@TNS_PDB01>select * from view_t1_t2;

  ID NAME	     TEL	     ADDR
---- --------------- --------------- ------------------------------
 101 xiaohong	     123456	     aaaaaaaaaaa
 102 xiaoming	     456789	     bbbbbbbb

创建TRIGGER
create or replace trigger tri_instead_view_t1_t2
        instead of insert on view_t1_t2
begin
        insert into t1(id,name) values (:new.id,:new.name);
        insert into t2(id,tel) values(:new.id,:new.tel);
end tri_instead_view_t1_t2;
/

测试TRIGGER
SCOTT@TNS_PDB01>select * from t1;

   ID NAME		     AGE
----- --------------- ----------
  101 xiaohong		      12
  102 xiaoming		      13

SCOTT@TNS_PDB01>select * from t2;

   ID TEL	      ADDR
----- --------------- ------------------------------
  101 123456	      aaaaaaaaaaa
  102 456789	      bbbbbbbb

SCOTT@TNS_PDB01>desc view_t1_t2;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 ID							     NOT NULL NUMBER
 NAME								      VARCHAR2(20)
 TEL								      VARCHAR2(15)
 ADDR								      VARCHAR2(30)

SCOTT@TNS_PDB01>insert into view_t1_t2 values(104,'xiaolong','123789','ccccccccc');

1 row created.

SCOTT@TNS_PDB01>select * from t1;

   ID NAME		     AGE
----- --------------- ----------
  101 xiaohong		      12
  102 xiaoming		      13
  104 xiaolong

SCOTT@TNS_PDB01>select * from t2;

   ID TEL	      ADDR
----- --------------- ------------------------------
  101 123456	      aaaaaaaaaaa
  102 456789	      bbbbbbbb
  104 123789

SCOTT@TNS_PDB01>select * from view_t1_t2;

   ID NAME	      TEL	      ADDR
----- --------------- --------------- ------------------------------
  101 xiaohong	      123456	      aaaaaaaaaaa
  102 xiaoming	      456789	      bbbbbbbb
  104 xiaolong	      123789

8、创建触发器，比较EMP_TEMP表中更新的工资（DML行级触发器）

创建测试表
SCOTT@TNS_PDB01>create table emp_temp as select * from emp;

Table created.

创建TRIGGER
create or replace trigger tri_dml_row_emp_temp
        before update on emp_temp
        for each row
begin
        if :old.sal > :new.sal then
                dbms_output.put_line('Sal decrease.');
        elsif :old.sal < :new.sal then
                dbms_output.put_line('Sal increase.');
        else
                dbms_output.put_line('Sal invariant.');
        end if;
        dbms_output.put_line('Before update''s sal : ' || :old.sal);
        dbms_output.put_line('After update''s sal : ' || :new.sal);
end tri_dml_row_emp_temp;
/

测试TRIGGER
SCOTT@TNS_PDB01>update emp_temp set sal = 1000 where empno = 7369;
Sal increase.
Before update's sal : 800
After update's sal : 1000

1 row updated.

9、创建TRIGGER将SCOTT用户上的DDL操作写入日志表LOG_DDL

创建测试表
create table log_ddl(obj_owner varchar2(30),obj_name varchar2(30),ddl_oper varchar2(30),ddl_date date);

SCOTT@TNS_PDB01>desc log_ddl;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -
 OBJ_OWNER							      VARCHAR2(30)
 OBJ_NAME							      VARCHAR2(30)
 DDL_OPER							      VARCHAR2(30)
 DDL_DATE							      DATE

创建TRIGGER
create or replace trigger tri_ddl_scott
        before ddl on schema
begin
  insert into log_ddl values(ora_dict_obj_owner,ora_dict_obj_name,ora_sysevent,sysdate);
end tri_ddl_scott;
/

测试TRIGGER
SCOTT@TNS_PDB01>create table t3 as select * from dual;

Table created.

SCOTT@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
SCOTT		T2		DROP			       2020-01-10 19:49:15
SCOTT		T2		CREATE			       2020-01-10 19:50:32
SCOTT		T2		TRUNCATE		       2020-01-10 19:51:42
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:52:56
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:53:21
SCOTT		T2		DROP			       2020-01-10 19:56:07
SCOTT		T3		CREATE			       2020-01-10 19:56:48

7 rows selected.

SCOTT@TNS_PDB01>create table t4 as select * from emp;

Table created.

SCOTT@TNS_PDB01>desc t4;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- -----------------------------------------
 EMPNO								      NUMBER(4)
 ENAME								      VARCHAR2(10)
 JOB								      VARCHAR2(9)
 MGR								      NUMBER(4)
 HIREDATE							      DATE
 SAL								      NUMBER(7,2)
 COMM								      NUMBER(7,2)
 DEPTNO 							      NUMBER(2)

SCOTT@TNS_PDB01>alter table t4 rename column job to t4_job;

Table altered.

SCOTT@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
SCOTT		T2		DROP			       2020-01-10 19:49:15
SCOTT		T2		CREATE			       2020-01-10 19:50:32
SCOTT		T2		TRUNCATE		       2020-01-10 19:51:42
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:52:56
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:53:21
SCOTT		T2		DROP			       2020-01-10 19:56:07
SCOTT		T3		CREATE			       2020-01-10 19:56:48
SCOTT		T4		CREATE			       2020-01-10 19:57:11
SCOTT		T4		ALTER			       2020-01-10 19:57:30

9 rows selected.

SCOTT@TNS_PDB01>conn sys/oracle@TNS_PDB01 as sysdba;
Connected.
SYS@TNS_PDB01>create table t5 as select * from dual;

Table created.

SYS@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
SCOTT		T2		DROP			       2020-01-10 19:49:15
SCOTT		T2		CREATE			       2020-01-10 19:50:32
SCOTT		T2		TRUNCATE		       2020-01-10 19:51:42
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:52:56
SCOTT		LOG_DDL 	ALTER			       2020-01-10 19:53:21
SCOTT		T2		DROP			       2020-01-10 19:56:07
SCOTT		T3		CREATE			       2020-01-10 19:56:48
SCOTT		T4		CREATE			       2020-01-10 19:57:11
SCOTT		T4		ALTER			       2020-01-10 19:57:30

10、监控数据库上所有用户的DDL操作并将其操作写入到SCOTT.LOG_DDL表上（DDL DATEBASE LEVEL TRIGGER）

创建用于写入DDL操作的日志表
SCOTT@TNS_PDB01>desc log_ddl;
 Name							     Null?    Type
 ----------------------------------------------------------- -------- 
 OBJ_OWNER							      VARCHAR2(30)
 OBJ_NAME							      VARCHAR2(30)
 DDL_OPER							      VARCHAR2(30)
 DDL_DATE							      DATE

创建TRIGGER
create or replace trigger tri_ddl_db
        before ddl on database
begin
  insert into scott.log_ddl values(ora_dict_obj_owner,ora_dict_obj_name,ora_sysevent,sysdate);
end tri_ddl_db;
/


测试TRIGGER
SCOTT@TNS_PDB01>select * from scott.log_ddl;

no rows selected

SCOTT@TNS_PDB01>select * from tab;

TNAME			  TABTYPE	 CLUSTERID
------------------------- ------------- ----------
DEPT			  TABLE
EMP			  TABLE
BONUS			  TABLE
SALGRADE		  TABLE
T1			  TABLE
LOG_DDL 		  TABLE
BIN$m8j3PWEr/nDgUxA7qMA2P TABLE
w==$0


7 rows selected.

SCOTT@TNS_PDB01>purge recyclebin;

Recyclebin purged.

SCOTT@TNS_PDB01>drop table t1 purge;

Table dropped.

SCOTT@TNS_PDB01>select * from scott.log_ddl;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55

SCOTT@TNS_PDB01>create table t2 as select * from emp;

Table created.

SCOTT@TNS_PDB01>select * from scott.log_ddl;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30

SCOTT@TNS_PDB01>alter table t2 rename column job to t1_job;

Table altered.

SCOTT@TNS_PDB01>select * from scott.log_ddl;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30
SCOTT		T2		ALTER			       2020-01-10 20:10:05

SCOTT@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30
SCOTT		T2		ALTER			       2020-01-10 20:10:05

SCOTT@TNS_PDB01>conn sys/oracle@TNS_PDB01 as sysdba;
Connected.

SYS@TNS_PDB01>drop table t3 purge;

Table dropped.

SYS@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30
SCOTT		T2		ALTER			       2020-01-10 20:10:05
SYS		T3		DROP			       2020-01-10 20:11:35

--可以看到SYS用户上的DDL操作也被记录到表LOG_DDL上

6 rows selected.

SYS@TNS_PDB01>create table t4 as select * from scott.dept;

Table created.

SYS@TNS_PDB01>drop table t4 purge;

Table dropped.

SYS@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ -------------------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30
SCOTT		T2		ALTER			       2020-01-10 20:10:05
SYS		T3		DROP			       2020-01-10 20:11:35
SYS		T4		CREATE			       2020-01-10 20:11:58
SYS		T4		DROP			       2020-01-10 20:12:06

8 rows selected.

SYS@TNS_PDB01>conn system/oracle@TNS_PDB01;
Connected.
SYSTEM@TNS_PDB01>create table t5 as select * from dual;

Table created.

SYSTEM@TNS_PDB01>drop table t5 purge;

Table dropped.

SYSTEM@TNS_PDB01>select * from scott.log_ddl order by ddl_date;

OBJ_OWNER	OBJ_NAME	DDL_OPER		       DDL_DATE
--------------- --------------- ------------------------------ --------
				PURGE			       2020-01-10 20:08:50
SCOTT		BIN$m8j3PWEr/nD DROP			       2020-01-10 20:08:50
		gUxA7qMA2Pw==$0

SCOTT		T1		DROP			       2020-01-10 20:08:55
SCOTT		T2		CREATE			       2020-01-10 20:09:30
SCOTT		T2		ALTER			       2020-01-10 20:10:05
SYS		T3		DROP			       2020-01-10 20:11:35
SYS		T4		CREATE			       2020-01-10 20:11:58
SYS		T4		DROP			       2020-01-10 20:12:06
SYSTEM		T5		CREATE			       2020-01-10 20:12:28
SYSTEM		T5		DROP			       2020-01-10 20:12:34

10 rows selected.

Lissen_Gan

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Oracle触发器常见实例

1、创建随数据库启动时自动启动PDB数据库（系统触发器）create or replace trigger trigger_sys_open_pdb01 after startup on databasebegin execute immediate 'alter pluggable database pdb01 open';end;/2、创建限制修改表...
复制链接

扫一扫