DDL触发器

1、

create or replace trigger tr_ddl

before ddl on database
declare
  l_errmsg varchar2(100) := '对不起，你无权修改';
begin
  if (Ora_dict_obj_type = 'TABLE' or Ora_dict_obj_type='INDEX' or Ora_dict_obj_type='PROCEDURE' or Ora_dict_obj_type='TRIGGER') AND
  /*(ora_dict_obj_owner ='NCCMOLTP_HNS' or ora_dict_obj_owner='NCCMHIST_HNS')  and*/  ora_login_user not in ('SYS', 'SYSTEM') and
     (Ora_sysevent = 'ALTER' OR Ora_sysevent = 'DROP' OR Ora_sysevent = 'CREATE') then
    raise_application_error(-20001,
                            ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' ||
                            l_errmsg);
  end if;
  
  /* if (Ora_dict_obj_type = 'TRIGGER' ) AND
  (ora_dict_obj_owner ='NCCMOLTP_HNS' or ora_dict_obj_owner='NCCMHIST_HNS')  and  ora_login_user not in ('SYS', 'SYSTEM') and
     (Ora_sysevent = 'ALTER' OR Ora_sysevent = 'DROP' or Ora_sysevent = 'CREATE') then
    raise_application_error(-20001,
                            ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' ||
                            l_errmsg);
  end if;*/


exception
  when no_data_found then
    null;

end;

2、

create or replace trigger tr_ddl
  before ddl on database
declare
  l_errmsg varchar2(100) := '请按照公司流程进行申请';
begin
  if (Ora_dict_obj_type = 'TABLE' or Ora_dict_obj_type = 'INDEX' or
     Ora_dict_obj_type = 'TRIGGER') AND
     ora_login_user not in ('SYS', 'SYSTEM') and
     (Ora_sysevent = 'ALTER' OR Ora_sysevent = 'DROP' OR
     Ora_sysevent = 'CREATE' OR (Ora_sysevent = 'TRUNCATE' and  sys_context('userenv','MODULE')='PL/SQL Developer')) then
    raise_application_error(-20001,
                            ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' ||
                            l_errmsg);
  end if;


exception
  when no_data_found then
    null;
end;

3、

CREATE trigger [ddl_trigger_server]  
on all server  
for  
    alter_Trigger,  
    drop_Trigger,
    alter_table 
as  
 DECLARE @v_name varchar(100);
 DECLARE @sql varchar(200);
 DECLARE @EVENTDATA XML;  
 set    @EVENTDATA=EVENTDATA();   
 select @v_name= @EVENTDATA.value ('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
 select @sql=@EVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') 
 if   @v_name='tri_update_hzyl_jtda_gr'  or CHARINDEX('tri_update_hzyl_jtda_gr',@sql)>0
 begin
 PRINT '省县数据同步使用,禁止对该触发器进行操作!'  
 ROLLBACK  
 end
GO