这个dll触发器有问题吗？

Question

create or replace trigger wp_create_synonyms_tg
  after create on schema
  
declare
vs_sql            varchar2(2000);
begin
  if ora_dict_obj_type = 'SYNONYM' then 
    return ;
  end if ;
  
  if ora_dict_obj_owner = 'EMIS51' then
    begin
      vs_sql := ' create or replace synonym emis550.'||ora_dict_obj_name||' for EMIS51.'||ora_dict_obj_name||';';
      
      execute immediate vs_sql ;
     
    exception
         when others then
           Raise_application_error(-20002,vs_sql||sqlerrm);
    end ;
         
    
  elsif ora_dict_obj_owner = 'EMIS550' then
   vs_sql := ' create or replace synonym emis55.'||ora_dict_obj_name||' for EMIS550.'||ora_dict_obj_name;
    execute immediate vs_sql ;
  end if ;
  
    
end wp_create_synonyms_tg;为什么这个trigger在触发的时候报错呢？报无效字符的错误
执行的如下的语句
create table aaa (a varchar2(10));

Answer 1

可以包含DDL的
动态语句不需要包含分号(;)的，第一个动态语句最后那个去掉看看

Answer 2

恩，楼上说的对，可以包含ddl，前提是使用自治事务
SQL> create or replace trigger tri_test
  2    after insert on test
  3    for each row
  4  declare
  5    -- local variables here
  6    v_sql VARCHAR2(100) ;
  7  begin
  8    v_sql:='create  table aaa(a number(10))';
  9    EXECUTE IMMEDIATE v_sql;
 10  end tri_test;
 11  /
 
Trigger created
 
SQL> insert into test values(4,'d',1);
 
insert into test values(4,'d',1)
 
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "TDS.TRI_TEST", line 6
ORA-04088: error during execution of trigger 'TDS.TRI_TEST'
--因为ddl隐含commit，导致插入失败
--下面使用自治事务SQL> create or replace trigger tri_test
  2    after insert on test
  3    for each row
  4  declare
  5    -- local variables here
  6    PRAGMA AUTONOMOUS_TRANSACTION;
  7    v_sql VARCHAR2(100) ;
  8  begin
  9    v_sql:='create  table aaa(a number(10))';
 10    EXECUTE IMMEDIATE v_sql;
 11    COMMIT;
 12  end tri_test;
 13  /
 
Trigger created
 
SQL> insert into test values(4,'d',1);
 
insert into test values(4,'d',1)
 
 
ORA-06512: at "TDS.TRI_TEST", line 7
ORA-04088: error during execution of trigger 'TDS.TRI_TEST'在sys用户下
GRANT CREATE ANY TABLE TO tds;
然后。
SQL> insert into test values(4,'d',1);
 
1 row inserted

Answer 3

楼上：
在表级触发器上不能包含显示或隐式提交/回滚操作
但在数据库级触发器上(ON SCHEMA)可以执行DDL操作，无需自治事务（自己做过，请权威回答）

Answer 4

新建单独的事物，报错30511 系统触发器中无效的DDL操作

Answer 5

试了下，可以用ddl
SQL> create or replace trigger wp_create_synonyms_tg
  2    after create on schema
  3  
  4  declare
  5  vs_sql            varchar2(2000);
  6  begin
  7    if ora_dict_obj_type = 'SYNONYM' then
  8      begin
  9        vs_sql := ' create table '||ora_dict_obj_name||'1 as select * from test';
 10  
 11        execute immediate vs_sql ;
 12  
 13      exception
 14           when others then
 15             Raise_application_error(-20002,vs_sql||sqlerrm);
 16      end ;
 17    end if ;
 18  
 19  END ;
 20  /
 
Trigger created
 
SQL> CREATE PUBLIC SYNONYM testtable FOR test ;
 
CREATE PUBLIC SYNONYM testtable FOR test
 
ORA-00955: name is already used by an existing object
 
SQL> drop public synonym testtable;
 
Synonym dropped
 
SQL> CREATE PUBLIC SYNONYM testtable FOR test ;
 
Synonym created
 
SQL> select * from testtable1;
 
CODE       NAME                      FCOUNT
---------- ---------- ---------------------
4          d                              1
1          A                              2
2          B                              1
3          C                              3
 
SQL> select * from test;
 
CODE       NAME                      FCOUNT
---------- ---------- ---------------------
4          d                              1
1          A                              2
2          B                              1
3          C                              3
 
SQL> 

Answer 6

这样还报错么？
create or replace trigger wp_create_synonyms_tg
  after create on schema
  
declare
vs_sql            varchar2(2000);
begin
  if ora_dict_obj_type = 'SYNONYM' then 
    return ;
  end if ;
  
  if ora_dict_obj_owner = 'EMIS51' then
    begin
      vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS51.'||ora_dict_obj_name||';';
      
      execute immediate vs_sql ;
     
    exception
         when others then
           Raise_application_error(-20002,vs_sql||sqlerrm);
    end ;
         
    
  elsif ora_dict_obj_owner = 'EMIS550' then
      vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS550.'||ora_dict_obj_name;
    execute immediate vs_sql ;
  end if ;
  
    
end wp_create_synonyms_tg;

Answer 7

忘记去分号了。你试试
create or replace trigger wp_create_synonyms_tg
  after create on schema
  
declare
vs_sql            varchar2(2000);
begin
  if ora_dict_obj_type = 'SYNONYM' then 
    return ;
  end if ;
  
  if ora_dict_obj_owner = 'EMIS51' then
    begin
      vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS51.'||ora_dict_obj_name;
      
      execute immediate vs_sql ;
     
    exception
         when others then
           Raise_application_error(-20002,vs_sql||sqlerrm);
    end ;
         
    
  elsif ora_dict_obj_owner = 'EMIS550' then
      vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS550.'||ora_dict_obj_name;
    execute immediate vs_sql ;
  end if ;
  
    
end wp_create_synonyms_tg;

Answer 8

还是没有用，还是报错 ora-30511 系统触发器中无效的DDL操作

Answer 9

用你这个运行的时候 报  权限不足  但是我单独的运行create table a1 as select * from a 
是可以运行的  难道系统触发器在执行ddl语句的时候需要什么特殊的权限？我的用户有DBA角色的权限 还不够吗？

Answer 10

供参考：http://blog.csdn.net/defonds/archive/2009/05/21/4205381.aspx

Answer 11

那些理论对于解决实际问题还有些差距
补充一点 我用的是oracle 9i 版本

Answer 12

可以创建的 我的是10g的
新建个用户给予dba权限完全可以的SQL> conn EMIS550/sys
已连接。
SQL> create or replace trigger wp_create_synonyms_tg
  2    after create on schema
  3    declare
  4  vs_sql            varchar2(2000);
  5  begin
  6    if ora_dict_obj_type = 'SYNONYM' then 
  7      return ;
  8    end if ;
  9    
 10    if ora_dict_obj_owner = 'EMIS51' then
 11  
 12     vs_sql := 'create or replace public synonym '||ora_dict_obj_name||' for EMIS51.'||ora_dict_o
bj_name;
 13     execute immediate vs_sql ;
 14    elsif ora_dict_obj_owner = 'EMIS550' then
 15        vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS550.'||ora_d
ict_obj_name;
 16      execute immediate vs_sql ;
 17    end if ;
 18   exception
 19           when others then
 20           Raise_application_error(-20002,vs_sql||sqlerrm);
 21  end wp_create_synonyms_tg;
 22  /触发器已创建

Answer 13

那您在emis51的用户下建一个表试试，看他在emis550里能不能建一个同义词，
create table AAA (a varchar2(10));

Answer 14

用sys用户把执行下 grant CREATE ANY SYNONYM to emis51
和  grant CREATE ANY table to emis51
好像在动态sql里执行ddl和直接执行ddl需要的权限不一样。

Answer 15

还是没有解决，报错：ora-30511系统触发器中无效的ddl操作

Answer 16

create or replace trigger wp_create_synonyms_tg
after create on database
declare
vs_sql            varchar2(2000);
begin
  if ora_dict_obj_type = 'SYNONYM' then
    return ;
  end if ;   if ora_dict_obj_owner = 'EMIS51' then     vs_sql := 'create or replace synonym '||ora_dict_obj_name||' for EMIS51.'||ora_dict_obj_name;
      execute immediate vs_sql ;
    elsif ora_dict_obj_owner = 'EMIS550' then
         vs_sql := ' create or replace public synonym '||ora_dict_obj_name||' for EMIS550.'||ora_dict_obj_name;
      execute immediate vs_sql ;
    end if ;
exception
     when others then
     Raise_application_error(-20002,vs_sql||sqlerrm);end wp_create_synonyms_tg;

Answer 17

无效DLL？
应该不会这个错误。
你这个不能在EMIS51用户下建（会报同名错误）
主要问题在于你创建表对象的时候触发了这个事件，而触发器里面同时引用了该表来定义新对象，可能会引发递归执行的错误（这时候表结构已经存在，可以在触发器中使用，但引用表为什么不行不懂，请高手看看）