时间戳

首页 > 解决方案 > 在创建表上触发为 sql

sql - 在创建表上触发为 sql

问题描述

我想将 SQL 创建语句保存如下：

CREATE TABLE tbl AS 
 SELECT 
  *
 FROM 
  tbl_info;

有没有办法将上一个查询保存在这样的表中：

table_name  query
tbl         CREATE TABLE tbl AS SELECT * FROM tbl_info;

which table_name, query 是该表的列，我试图查找触发器，但在 create 语句之前或之后没有关于触发器的内容，请帮助。

标签： sqloracleplsqltriggers

解决方案

确实有一种方法可以做到这一点，但不推荐。Oracle 提供的开箱即用审计功能比您能想到的任何定制解决方案都要好得多。

话虽如此，一种选择是使用DDL TRIGGER. 在下面的示例中，您有一个用于存储create事件的审计表和一个trigger用于记录它们的审计表。

请记住，我使用ON SCHEMA，因此它只会影响触发器所属架构的 CREATE 事件。

基本代码

CREATE TABLE AUDIT_DDL (
  D DATE,
  OSUSER VARCHAR2(255),
  CURRENT_USER VARCHAR2(255),
  SYSEVENT VARCHAR2(30), 
  STATEMENTS VARCHAR2(1000)
  );

CREATE OR REPLACE TRIGGER AUDIT_DDL_TRG
  AFTER DDL ON SCHEMA
DECLARE
  sql_text ora_name_list_t;
  v_stmt   VARCHAR2(2000);
  n        PLS_INTEGER;
BEGIN
  n := ora_sql_txt(sql_text);
  FOR i IN 1 .. n LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;
v_stmt :=regexp_replace(v_stmt,
                        'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)',
                        '\1',
                        1,
                        1,
                        'i');
  IF (ORA_SYSEVENT = 'CREATE') 
  THEN
    INSERT INTO AUDIT_DDL
      (D,
       OSUSER,
       CURRENT_USER,
       SYSEVENT,
       STATEMENTS)
    VALUES
      (SYSDATE,
       SYS_CONTEXT('USERENV', 'OS_USER'),
       SYS_CONTEXT('USERENV', 'CURRENT_USER'),
       ORA_SYSEVENT,
       v_stmt);
  END IF;
END;
/

让我们看看它是如何工作的

sqlplus test1/Oracle_123

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 3 11:13:21 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Sep 22 2021 08:08:57 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table t1 as select * from all_objects ;
create table t1 as select * from all_objects
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from all_objects ;

Table created.

SQL> select d,statements from AUDIT_DDL ;

D
---------
STATEMENTS
--------------------------------------------------------------------------------
03-OCT-21
create table t1 as select * from all_objects

正如您在上面看到的（故意），我做了几条语句（创建失败，删除表，最后是CTAS语句）。但是，我们的事件触发器只是寻找syseventcreate，因为它是after ddl on schema，它只会存储命令执行成功后的数据。

您可以存储默认上下文的许多其他属性sys_context。

但是，这对性能有很大影响，不推荐

推荐阅读