DDL Triggers |
- Monitor significant events in the database.
- Monitor errant code that can that can corrupt or destabilize your database.
- Use these in development, test, and stage systems to understand and monitor the dynamics of database activities.
- Also useful when you patch your application code. They can let you find
potential changes between releases. - During an upgrade: use instead-of create trigger to enforce table creation storage clauses or partitioning rules.
- Track the creation and modification of tables by application programs that lead to database fragmentation.
- Effective security tools: monitor GRANT and REVOKE privilege statements.
When can they fire? DDL Events |
---|
ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DDL (means: ANY DDL event), DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE |
System-defined event attribute functions | |
---|---|
ORA_CLIENT_IP_ADDRESS | Returns the client IP address as a VARCHAR2.DECLARE ip_address VARCHAR2(11); BEGIN IF ora_sysevent = 'LOGON' THEN ip_address := ora_client_ip_address; END IF; END; |
ORA_DATABASE_NAME | Returns the database name.BEGIN (...) db_name := ora_database_name; (...) END; |
ORA_DES_ENCRYPTED_PASSWORD | - Returns the DES-encrypted password as VARCHAR2. - Equivalent to the value in the SYS.USER$ table PASSWORD col (11g). - Passwds are no longer accessible in DBA_USERS or ALL_USERS BEGIN IF ora_dict_obj_type = 'USER' THEN password := ora_des_encrypted_password; END IF; END; |
ORA_DICT_OBJ_NAME | Returns the name of the object target of the DDL statement.DECLARE database VARCHAR2(50); BEGIN database := ora_obj_name; END; |
ORA_DICT_OBJ_NAME_LIST | The function returns the number of elements in the list as a PLS_INTEGER datatype. The name_list contains the list of object names touched by the triggering event.DECLARE name_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_name_list(name_list); END IF; END; |
ORA_DICT_OBJ_OWNER | returns an owner of the object acted upon by the event.database := ora_dict_obj_owner; |
ORA_DICT_OBJ_OWNER_LIST | formal parameter datatype is defined in the DBMS_STANDARD package as ORA_NAME_LIST_T (table of varchar2(64)). The function returns the number of elements in the list. The owner_list contains the list of owners of objects affected by the event. DECLARE owner_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_owner_list(owner_list); END IF; END; |
ORA_DICT_OBJ_TYPE | |
ORA_GRANTEE | |
ORA_INSTANCE_NUM | |
ORA_IS_ALTER_COLUMN | |
ORA_IS_CREATING_NESTED_TABLE | BEGIN IF ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table THEN INSERT INTO logging_table VALUES (ora_dict_obj_name||'.'||' created with nested table.'); END IF; END; |
ORA_IS_DROP_COLUMN | |
ORA_IS_SERVERERROR | |
ORA_LOGIN_USER> | |
ORA_PARTITION_POS | returns the numeric position with the SQL text where you can insert a partition clause. This is only available in an INSTEAD OF CREATE trigger.DECLARE sql_text ORA_NAME_LIST_T; sql_stmt VARCHAR2(32767); partition VARCHAR2(32767) := 'partitioning_clause'; BEGIN FOR i IN 1..ora_sql_txt(sql_text) LOOP sql_stmt := sql_stmt || sql_text(i); END LOOP; sql_stmt := SUBSTR(sql_text,1,ora_partition_pos – 1) ||' ' || partition||' '|| SUBSTR(sql_test,ora_partition_pos); -- Add logic to prepend schema because -- this runs under SYSTEM. sql_stmt := REPLACE(UPPER(sql_stmt),'CREATE TABLE ' ,'CREATE TABLE '||ora_login_user||'.'); EXECUTE IMMEDIATE sql_stmt; END; |
ORA_PRIVILEGE_LIST | Returns list of privileges granted/revoked. |
ORA_REVOKEE | |
ORA_SERVER_ERROR | |
ORA_SERVER_ERROR_DEPTH | |
ORA_SERVER_ERROR_MSG | |
ORA_SERVER_ERROR_NUM_PARAMS | |
ORA_SERVER_ERROR_PARAM | |
ORA_SQL_TXT | Returns the substring of the processed SQL statement that triggered the event. |
ORA_SYSEVENT | |
ORA_WITH_GRANT_OPTION | |
SPACE_ERROR_INFO |
DDL Triggers: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA} [WHEN (logical_expression)] [DECLARE] declaration_statements; BEGIN execution_statements; END [trigger_name]; /
![]() | DDL Trigger on Creation statements |
(a) create table audit_creation
(b) create sequence audit_creation_s1
(c) Create trigger audit_creation
(d) create a synonym to fire the trigger
(e) check the inserted row on audit_creation
CREATE TABLE audit_creation ( audit_creation_id NUMBER PRIMARY KEY, audit_owner_name VARCHAR2(30) NOT NULL, audit_obj_name VARCHAR2(30) NOT NULL, audit_date DATE NOT NULL); CREATE SEQUENCE audit_creation_s1; CREATE OR REPLACE TRIGGER audit_creation BEFORE CREATE ON SCHEMA BEGIN insert into audit_creation values (audit_creation_s1.nextval, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME, sysdate); END audit_creation; SQL> Create synonym empsym for hr.employees; synonym created. SQL> Select * from audit_creation; AUDIT_CREATION_ID AUDIT_OWNER_NAME AUDIT_OBJ_NAME AUDIT_DATE ------------------- ---------------------- ------------------- --------------- 1 HR EMPSYN 22-AUG-10
No comments:
Post a Comment