Блог

Главная - Вопросы по программированию - Триггер для создания таблицы как sql

Триггер для создания таблицы как sql

#sql #oracle #plsql #triggers

Вопрос:

Я хочу сохранить инструкцию SQL create следующим образом:

 CREATE TABLE tbl AS 
 SELECT 
  *
 FROM 
  tbl_info;

Есть ли способ сохранить предыдущий запрос в таблице, подобной этой:

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

какое имя таблицы, запрос являются столбцами этой таблицы,
я пытался найти триггеры, но в инструкции create нет ничего о триггере до или после,
пожалуйста, помогите.

Комментарии:

1. вы пытаетесь сохранить sql, выданный всеми пользователями? или вы просто пытаетесь сохранить (одно )конкретное утверждение? трудно понять, что вы пытаетесь сделать . можете ли вы предоставить более подробную информацию?

2. @Мигель да, все заявления о создании выпущены.

3. Вы не сможете сделать это с помощью триггера, который вам понадобится для включения трассировки базы данных, но это приведет к снижению производительности. Или вы можете использовать v$sql в определенной степени.

4. @loyalTurkman, это можно сделать с помощью триггера. смотрите мой ответ ниже.

Ответ №1:

Действительно, есть способ сделать это, но он не рекомендуется. Oracle предлагает готовые возможности аудита намного лучше, чем любое пользовательское решение, которое вы можете придумать.

Сказав это, одним из вариантов было бы использовать a DDL TRIGGER . В приведенном ниже примере у вас есть таблица аудита для хранения create событий и a trigger для их записи.

Имейте в виду , что я использую ON SCHEMA , поэтому это повлияет только на события СОЗДАНИЯ для схемы, к которой принадлежит триггер.

Базовый код

 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 , инструкция ). Однако наш триггер события ищет только sysevent команду create, и поскольку это так after ddl on schema , он будет хранить данные только после успешного выполнения команды.

Вы можете сохранить множество других свойств контекста по умолчанию sys_context .

Тем не менее, это оказывает огромное влияние на производительность и не рекомендуется

Метки: