create [or replace] trigger 用户名.触发器名
       {before|after} {DDL事件} on {database|schema}
       [when 条件]
declare
  定义变量。
begin
  PL/SQL语句块。
end;
复制

-- Create table
createtable SYSTEM.PROTECTED_OBJECT
(
  OWNER VARCHAR2(30) notnull,
  OBJECT_TYPE VARCHAR2(30) notnull,
  OBJECT_NAME VARCHAR2(30) notnull,
  ENABLED VARCHAR2(3) notnull,
  DEFINE_TIME DATEdefaultsysdatenotnull,
  DEFINER VARCHAR2(32) notnull
)
tablespaceUSERS;
 -- Create/Recreate primary, uniqueand foreign keyconstraints
altertable SYSTEM.PROTECTED_OBJECT
addconstraint PK_PROTECTED_OBJECT primary key (OWNER, OBJECT_TYPE, OBJECT_NAME)
usingindex
tablespaceUSERS;
-- Create/Recreate checkconstraints
altertable SYSTEM.PROTECTED_OBJECT
addcheck (enabled in ( 'YES', 'NO' ));

commentoncolumn SYSTEM.PROTECTED_OBJECT.ENABLED
is'是否限制ddl操作，YES标示限制，NO标示不限制';
复制

create or replace trigger SYS.trg_stop_ddl
  before drop or truncate or alter on database
declare
  v_ObjectCount integer := 0;
  v_SqlList ora_name_list_t;
  v_ActionSql clob := '';
  v_ListDepth integer := 0;
  v_Session v$session%rowtype;
  v_Message varchar2(256);
  v_action_drop integer := 0;
  v_action_truncate integer := 0;
  v_sysevent varchar2(256);
begin
  -- Check the pre-defined table to see whether this object can be modified
  select count(*) into v_ObjectCount
  from system.protected_object o
  where o.enabled = 'YES'
    and upper( o.owner ) = ora_dict_obj_owner
    and upper( o.object_type ) = ora_dict_obj_type
    and upper( o.object_name ) = ora_dict_obj_name;
  --
if v_ObjectCount > 0then
    -- This object can not be modified
    select * into v_Session from v$session a
      where sid = ( select sid from v$mystat where rownum = 1 );

    -- Get the DDL sql text
    v_ListDepth := ora_sql_txt( v_SqlList );
    if v_ListDepth > 0then
      for i in1..v_ListDepth loop
        v_ActionSql := v_ActionSql || v_SqlList( i );
      end loop;
    endif;
    --
    v_action_drop:=instr(lower(v_ActionSql),'drop partition');
    v_action_truncate:=instr(lower(v_ActionSql),'truncate partition');
    v_sysevent:=ora_sysevent;
    --
    if (v_action_drop>0and v_sysevent='ALTER')then
       raise_application_error( -20001, 'DDL drop partition not allowed on this object:'||ora_dict_obj_name||': please contact DBA.');
    elsif (v_action_truncate>0and v_sysevent='ALTER') then
       raise_application_error( -20001, 'DDL truncate partition not allowed on this object:'||ora_dict_obj_name||', please contact DBA.'); 
    elsif (v_sysevent='DROP'or v_sysevent='TRUNCATE') then
      -- Write message to alert log
      sys.dbms_system.ksdwrt( 2, 'ORA-20001 DDL operation not allowed' );
      v_Message := 'user='|| ora_login_user ||'; os_user='|| v_Session.Osuser
                   ||'; program='|| v_Session.Program ||'; machine='|| v_Session.Machine;
      sys.dbms_system.ksdwrt( 2, 'ORA-20002 '|| v_Message );
      sys.dbms_system.ksdwrt( 2, 'ORA-20003 DDL: '|| v_ActionSql );
      
      -- Raise error to user
      raise_application_error( -20001, 'DDL operation not allowed on this object :'||ora_dict_obj_name||', please contact DBA.');
    endif; 
endif;

end trg_stop_ddl;
/
复制

update system.protected_object t set t.enabled='NO';
commit;
复制

update system.protected_object t set t.enabled='YES';

commit;
复制

alter trigger SYS.TRG_STOP_DDL disable;
--
alter trigger SYS.TRG_STOP_DDL enable;
复制