OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17: There is high demand for professionals in the information technology (IT) industry, andOracle certifications are the hottest credential in the database world. You have made theright decision to pursue certification, because being Oracle Database 11g certified will giveyou a distinct advantage in this highly competitive market.
Nội dung trích xuất từ tài liệu:
OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17 Identifying PL/SQL Objects 731 ,’No grant to PUBLIC allowed for ‘ ||DICTIONARY_OBJ_OWNER||’.’ ||DICTIONARY_OBJ_NAME); END IF; END LOOP;END; In the preceding example, the DDL event is a GRANT statement issued by user engineering.The code examines the grantee list, and if it finds the special user/role PUBLIC, an exceptionis raised, causing the grant to fail. Table 13.2 shows the DDL trigger events.ta b l e 1 3 . 2 DDL Trigger EventsEvent When It Fires[BEFORE/AFTER] ALTER When an ALTER statement changes a database object[BEFORE/AFTER] ANALYZE When the database gathers or deletes statistics or validates the structure of an object[BEFORE/AFTER] ASSOCIATE When the database associates a statistic with a databaseSTATISTICS object with an ASSOCIATE STATISTICS statement[BEFORE/AFTER] AUDIT When the database records an audit action (except FGA)[BEFORE/AFTER] COMMENT When a comment on a table or column is modified[BEFORE/AFTER] CREATE When the database object is created[BEFORE/AFTER] DDL In conjunction with any of the following: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE[BEFORE/AFTER] When a database disassociates a statistic type from a databaseDISASSOCIATE STATISTICS object with a DISASSOCIATE STATISTICS statement[BEFORE/AFTER] DROP When a DROP statement removes an object from the database[BEFORE/AFTER] GRANT When a GRANT statement assigns a privilege[BEFORE/AFTER] NOAUDIT When a NOAUDIT statement changes database auditing[BEFORE/AFTER] RENAME When a RENAME statement changes an object name[BEFORE/AFTER] REVOKE When a REVOKE statement rescinds a privilege[BEFORE/AFTER] TRUNCATE When a TRUNCATE statement purges a table732 Chapter 13 N Managing Data and UndoDatabase Trigger EventsDatabase event triggers fire when the specified database-level event occurs. Most of thesetriggers are available only before or after the database event, but not both. The following example creates an after-server error trigger that sends an email notifica-tion when an ORA-01555 error occurs:CREATE OR REPLACE TRIGGER Email_on_1555_ErrAFTER SERVERERROR ON DATABASEDECLARE mail_conn UTL_SMTP.connection; smtp_relay VARCHAR2(32) := ‘mailserver’; recipient_address VARCHAR2(64) := ‘DBA@hotmail.com’; sender_address VARCHAR2(64) := ‘oracle@sybex.com’; mail_port NUMBER := 25; msg VARCHAR2(200);BEGIN IF USER = ‘SYSTEM’ THEN -- Ignore this error NULL; ELSIF IS_SERVERERROR (1555) THEN -- compose the message msg := ‘Subject: ORA-1555 error’; msg := msg||’Snapshot too old err at ‘||systimest -- send email notice mail_conn := UTL_SMTP.open_connection(smtp_relay ,mail_port); UTL_SMTP.HELO(mail_conn, smtp_relay); UTL_SMTP.MAIL(mail_conn, sender_address); UTL_SMTP.RCPT(mail_conn, recipient_address); UTL_SMTP.DATA(mail_conn, msg); UTL_SMTP.QUIT(mail_conn); END IF;END; Be careful when using database triggers. Fully test them in development before deployingthem to production. Table 13.3 shows the database trigger events.ta b l e 1 3 . 3 Database Trigger EventsEvent When It FiresAFTER LOGON When a database session is established—only the AFTER trigger is allowedBEFORE LOGOFF When a database session ends normally—only the BEFORE trigger is allowed Identifying PL/SQL Objects 733ta b l e 1 3 . 3 Database Trigger Events (continued)Event When It FiresAFTER STARTUP When the database is opened—only the AFTER trigger is allowedBEFORE SHUTDOWN When the database is closed—only the BEFORE trigger is allowedAFTER SERVERERROR When a database exception is raised—only the AFTER trigger is allowedAFTER SUSPEND When a server error causes a transaction to be suspended—only the AFTER trigger is allowedEnabling and Disabling TriggersThe database automatically enables a trigger when you create it. After creating a trigger,you can disable (temporarily prevent it from firing) or reenable it. You can disable andenable triggers by name with an ALTER TRIGGER statement. Here are two examples:ALTER TRIGGER after ...