从 all_tables 获取触发器的表名

发布于 2024-12-26 06:12:32 字数 423 浏览 0 评论 0原文

我们有一个应用程序，它创建一个具有随机生成名称的表。我想在这个表上创建一个触发器。由于我不知道该表的名称，我想从 all_table 视图中获取它。我怎样才能实现这样的目标？

    create or replace trigger t1      
after insert or update on (select table_name from all_tables where owner = 'CustomAPP' and table_name like 'STAGE_%')  
     -- for each row
    declare
      -- local variables here
    begin

    end t1;

上面的 SQL 显然会报错，因为 create 触发器后面的 select 子句而不是表名。请指教

原文

We have an application that creates a table with a randomly generated name. I would like to create a trigger on this table.Since I do not know the name of the tabe I would like to get it from the all_table view. How can I go about achieveing something like this?

    create or replace trigger t1      
after insert or update on (select table_name from all_tables where owner = 'CustomAPP' and table_name like 'STAGE_%')  
     -- for each row
    declare
      -- local variables here
    begin

    end t1;

The SQL above obviously gives an error because of the select clause after the create trigger instead of a table name. Please advise

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玩套路吗 2025-01-02 06:12:32

为此，您需要使整个 CREATE TRIGGER 动态化。像这样的东西应该有效。您可能希望使触发器名称取决于表的名称，假设可能有多个表，您对 ALL_TABLES 的查询可能会返回多行。而且您当然希望触发器能够执行某些操作，而不是空着身体。

SQL> create table stg_12345( col1 number );

Table created.

SQL> begin
  2    for x in (select *
  3                from user_tables
  4               where table_name like 'STG%')
  5    loop
  6      execute immediate
  7        'create or replace trigger trg_foo ' ||
  8        ' before insert on ' || x.table_name ||
  9        ' for each row ' ||
 10        'begin ' ||
 11        '  null; ' ||
 12        'end;';
 13    end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> select count(*) from user_triggers where trigger_name = 'TRG_FOO';

  COUNT(*)
----------
         1

当然，动态创建表的应用程序的想法让我感到非常害怕。如果您对此有任何控制权，我强烈建议重新考虑架构。

You would need to make the entire CREATE TRIGGER dynamic in order to do this. Something like this should work. You probably want to make the trigger name depend on the name of the table assuming there could be multiple tables that your query against ALL_TABLES might return multiple rows. And you certainly want the trigger to do something rather than having an empty body.

SQL> create table stg_12345( col1 number );

Table created.

SQL> begin
  2    for x in (select *
  3                from user_tables
  4               where table_name like 'STG%')
  5    loop
  6      execute immediate
  7        'create or replace trigger trg_foo ' ||
  8        ' before insert on ' || x.table_name ||
  9        ' for each row ' ||
 10        'begin ' ||
 11        '  null; ' ||
 12        'end;';
 13    end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> select count(*) from user_triggers where trigger_name = 'TRG_FOO';

  COUNT(*)
----------
         1

Of course, the idea of an application that creates tables on the fly is one that frightens me to the core. If you have any control over that, I would strongly suggest reconsidering the architecture.

回复收藏 0 原文

鸢与 2025-01-02 06:12:32

解决方案 1：

如果问题是“由于缺乏统计信息而导致性能不佳”，那么在系统或会话级别更改 OPTIMIZER_DYNAMIC_SAMPLING 参数也许会有所帮助。有关更全面的讨论，请参阅性能调优指南，但我发现默认值 2（64 个块）是不够的，特别是对于大型数据集来说，保持优化器统计信息最新是不切实际的。

解决方案 2：

如果您确实希望在创建表后自动创建触发器，则需要为架构创建 DDL 触发器。下面的 SQL 演示了这一点。

CREATE OR REPLACE TRIGGER MAKE_ME_A_TRIGGER
AFTER CREATE ON CUSTOM_APP_SCHEMA
AS
  l_trigger_sql varchar2(4000);
BEGIN  
  if l_ora_obj_dict_type = 'TABLE'
  then
    l_trigger_sql := 'create or replace trigger ' || ora_dict_obj_name
                     ' before insert on ' || ora_dict_obj_type||   
                     ' for each row ' ||  
                     'begin ' ||  
                     '  null; ' ||
                     'end;'
    execute immediate l_sql;
  end if;
END;
/

Solution 1:

If the problem is "poor performance due to lack of statistics", perhaps changing the OPTIMIZER_DYNAMIC_SAMPLING parameter at a system or session level can help. See the Performance Tuning Guide for a more thorough discussion, but I've found the default of 2 (64 blocks) to be insufficient, especially for large data sets where keeping optimizer statistics current is impractical.

Solution 2:

If you really want to automatically create a trigger after a table's been created, you'll need to create a DDL trigger for the schema. The SQL below demonstrates that.

CREATE OR REPLACE TRIGGER MAKE_ME_A_TRIGGER
AFTER CREATE ON CUSTOM_APP_SCHEMA
AS
  l_trigger_sql varchar2(4000);
BEGIN  
  if l_ora_obj_dict_type = 'TABLE'
  then
    l_trigger_sql := 'create or replace trigger ' || ora_dict_obj_name
                     ' before insert on ' || ora_dict_obj_type||   
                     ' for each row ' ||  
                     'begin ' ||  
                     '  null; ' ||
                     'end;'
    execute immediate l_sql;
  end if;
END;
/

回复收藏 0 原文