应用里面有一个表每天从别的系统导全量数据入库,所以这个表每天会进行一次truncate操作,但这个表里面有几条人为构造的数据想保留,于是想到是否可以实现DDL操作后触发DML操作,上网一查果然有现成案例,简单实现方式如下:
--create table create table lbx_1(id int); --create trigger create or replace trigger trg_lbx_1 after truncate on database begin if ora_sysevent='TRUNCATE' and ORA_DICT_OBJ_NAME='LBX_1' then insert into lbx_1 values(9999); end if; end;
不过看情况代价还是有点大,每次DDL操作都去做一次判断.
以下变量可直接使用:
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;