能否读对TRUNCATE/DROP/ALTER TABLE 建立触发器

SHSUMMER

能否读对TRUNCATE/DROP/ALTER TABLE 建立触发器

cjf107

对ddl的触发器

SHSUMMER

最初由 cjf107 发布
[B]对ddl的触发器 [/B]

怎么写呢？

SHSUMMER

up

lcmlsj

DDL triggers can be associated with the database or with a schema. Their attributes include the system event, the type of schema object, and its name. They can specify simple conditions on the type and name of the schema object, as well as functions like USERID and USERNAME. DDL triggers include the following types of triggers:

BEFORE CREATE and AFTER CREATE triggers fire when a schema object is created in the database or schema.

BEFORE ALTER and AFTER ALTER triggers fire when a schema object is altered in the database or schema.

BEFORE DROP and AFTER DROP triggers fire when a schema object is dropped from the database or schema.

lcmlsj

SQL> create or replace trigger ddl_deny
  2  before alter or drop or truncate on database
  3  declare
  4    l_errmsg varchar2(100):= 'You have no permission to this operation';
  5  begin
  6    if ora_sysevent = '' then
  7       raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  8    elsif ora_sysevent = 'ALTER' then
  9      raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
10    elsif ora_sysevent = 'DROP' then
11      raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
12    elsif ora_sysevent = 'TRUNCATE' then
13      raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
14    end if;
15  
16  exception
17    when no_data_found then
18      null;
19  end;
20  /

Trigger created.

SQL> create table t
  2  as
  3  select * from dba_objects;

Table created.

SQL> truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: SYSTEM.T You have no permission to this operation
ORA-06512: at line 11

SHSUMMER

谢谢，我要对某张表truncatre/DROP/ALTER的时候，才触发

zhang41082

那就要在DDL触发器中判断，你的操作是对哪个对象的。
Name
Description

ORA_CLIENT_IP_ADDRESS
Returns the IP address of the client.

ORA_DATABASE_NAME
Returns the name of the database.

ORA_DES_ENCRYPTED_PASSWORD
Returns the DES-encrypted password of the current user.

ORA_DICT_OBJ_NAME
Returns the name of the database object affected by the firing DDL.

ORA_DICT_OBJ_NAME_LIST
Returns the count of objects affected. It also returns a complete list of objects affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_DICT_OBJ_OWNER
Returns the owner of the database object affected by the firing DDL.

ORA_DICT_OBJ_OWNER_LIST
Returns the count of objects affected. It also returns a complete list of object owners affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_DICT_OBJ_TYPE
Returns the type of database object affected by the firing DDL (e.g., TABLE or INDEX).

ORA_GRANTEE
Returns the count of grantees. The USER_LIST argument contains the full list of grantees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_INSTANCE_NUM
Returns the number of the database instance.

ORA_IS_ALTER_COLUMN
Returns TRUE if the specified COLUMN_NAME argument is being altered, or FALSE if not.

ORA_IS_CREATING_NESTED_TABLE
Returns TRUE if a nested table is being created, or FALSE if not.

ORA_IS_DROP_COLUMN
Returns TRUE if the specified COLUMN_NAME argument is indeed being dropped, or FALSE if not.

ORA_LOGIN_USER
Returns the name of the Oracle user for which the trigger fired.

ORA_PARTITION_POS
Returns the position in the SQL command where a partitioning clause could be correctly added.

ORA_PRIVILEGE_LIST
Returns the number of privileges being granted or revoked. The PRIVILEGE_LIST argument contains the full list of privileges affected, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_REVOKEE
Returns the count of revokees. The USER_LIST argument contains the full list of revokees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_SQL_TXT
Returns the number of lines in the SQL statement firing the trigger. The SQL_TXT argument returns each line of the statement, which is an argument of type DBMS_STANDARD.ORA_NAME_LIST_T.

ORA_SYSEVENT
Returns the type of event that caused the DDL trigger to fire (e.g., CREATE, DROP, or ALTER).

ORA_WITH_GRANT_OPTION
Returns TRUE if privileges were granted with the GRANT option, or FALSE if not.

举例：IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'Can modify database,pls contact dba to do this');
  END IF;
这个时候就是判断如果你的DDL操作的是表，则抛出异常

SHSUMMER

谢谢，我是想要指定当truncate table test的时候，才触发

SHSUMMER

就是要truncate表test的时候才触发，而truncate别的表的时候不触发