CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
loyal Turkman
loyal Turkman

Reputation: 33

Trigger on the create table as sql

I want to save the SQL create statement as following:

CREATE TABLE tbl AS 
 SELECT 
  *
 FROM 
  tbl_info;

Is there a way to save the previous query in a table like this:

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

which table_name, query are the columns of this table, i tried to look up for triggers but there is nothing about trigger before or after on create statement, please your help.

Upvotes: 0

Views: 554

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Indeed there is a way of doing this, but it is not recommendable. Oracle offers out of the box audit capabilities much better than any custom solution you can think of.

Having said that, one option would be to use a DDL TRIGGER. In the example below you have an audit table to store the create event and a trigger to record them.

Keep in mind that I use ON SCHEMA, so it will only affect the CREATE events for the schema which the trigger belongs to.

Base code

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;
/

Let's check how it works

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

As you can see above ( on purpose ) , I did several statements ( a create failing, a drop table, and finally a CTAS statement ). However, our event trigger is only looking for the sysevent create, and because it is after ddl on schema, it will only store the data after the command is executed successfully.

You can store many other properties of the default context sys_context.

Nevertheless, this has a huge impact in performance and it is not recommendable

Upvotes: 2

Related Questions