Responding to Database Events Through Triggers|oracle sql and pl sql training in chennai

Responding to Database Events Through Triggers

Note:
This topic applies only to simple triggers.

Database event publication lets applications subscribe to database events, just like they subscribe to messages from other applications. The database events publication framework includes the following features:

Infrastructure for publish/subscribe, by making the database an active publisher of events.
Integration of data cartridges in the server. The database events publication can be used to notify cartridges of state changes in the server.
Integration of fine-grained access control in the server.

By creating a trigger, you can specify a subprogram that runs when an event occurs. DML events are supported on tables, and database events are supported on DATABASE and SCHEMA. You can turn notification on and off by enabling and disabling the trigger using the ALTER TRIGGER statement.

This feature is integrated with the Advanced Queueing engine. Publish/subscribe applications use the DBMS_AQ.ENQUEUE procedure, and other applications such as cartridges use callouts.

Topics:

How Events Are Published Through Triggers
Publication Context
Error Handling
Execution Model
Event Attribute Functions
Database Events
Client Events

How Events Are Published Through Triggers

When the database detects an event, the trigger mechanism executes the action specified in the trigger. The action can include publishing the event to a queue so that subscribers receive notifications. To publish events, use the DBMS_AQ package.

Note:
The database can detect only system-defined events. You cannot define your own events.

When it detects an event, the database fires all triggers that are enabled on that event, except the following:

Any trigger that is the target of the triggering event.

For example, a trigger for all DROP events does not fire when it is dropped itself.

Any trigger that was modified, but not committed, within the same transaction as the triggering event.

For example, recursive DDL within a system trigger might modify a trigger, which prevents the modified trigger from being fired by events within the same transaction.

Publication Context

When an event is published, certain run-time context and attributes, as specified in the parameter list, are passed to the callout subprogram. A set of functions called event attribute functions are provided.

For each supported database event, you can identify and predefine event-specific attributes for the event. You can choose the parameter list to be any of these attributes, along with other simple expressions. For callouts, these are passed as IN arguments.

Error Handling

Return status from publication callout functions for all events are ignored. For example, with SHUTDOWN events, the database cannot do anything with the return status.

Execution Model

Traditionally, triggers execute as the definer of the trigger. The trigger action of an event is executed as the definer of the action (as the definer of the package or function in callouts, or as owner of the trigger in queues). Because the owner of the trigger must have EXECUTE privileges on the underlying queues, packages, or subprograms, this action is consistent.

Event Attribute Functions

When the database fires a trigger, you can retrieve certain attributes about the event that fired the trigger. You can retrieve each attribute with a function call. Table 9-3 describes the system-defined event attributes.

Note:

The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.

In earlier releases, these functions were accessed through the SYS package. Oracle recommends you use these public synonyms whose names begin with ora_.

ora_name_list_t is defined in package DBMS_STANDARD as

TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

Table 9-3 System-Defined Event Attributes

Attribute	Type	Description	Example
ora_client_ip_address	VARCHAR2	Returns IP address of the client in a LOGON event when the underlying protocol is TCP/IP	DECLARE v_addr VARCHAR2(11); BEGIN IF (ora_sysevent = 'LOGON') THEN v_addr := ora_client_ip_address; END IF; END;
ora_database_name	VARCHAR2(50)	Database name.	DECLARE v_db_name VARCHAR2(50); BEGIN v_db_name := ora_database_name; END;
ora_des_encrypted_password	VARCHAR2	The DES-encrypted password of the user being created or altered.	IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF;
ora_dict_obj_name	VARCHAR(30)	Name of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table VALUES ('Changed object is ' \|\| ora_dict_obj_name);
ora_dict_obj_name_list (name_list OUT ora_name_list_t)	PLS_INTEGER	Return the list of object names of objects being modified in the event.	DECLARE name_list DBMS_STANDARD.ora_name_list_t; number_modified PLS_INTEGER; BEGIN IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(name_list); END IF; END;
ora_dict_obj_ow	VARCHAR(30)	Owner of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table VALUES ('object owner is' \|\| ora_dict_obj_owner);
ora_dict_obj_owner_list (owner_list OUT ora_name_list_t)	PLS_INTEGER	Returns the list of object owners of objects being modified in the event.	DECLARE owner_list DBMS_STANDARD.ora_name_list_t; number_modified PLS_INTEGER; BEGIN IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(owner_list); END IF; END;
ora_dict_obj_type	VARCHAR(20)	Type of the dictionary object on which the DDL operation occurred.	INSERT INTO event_table VALUES ('This object is a ' \|\| ora_dict_obj_type);
ora_grantee (user_list OUT ora_name_list_t)	PLS_INTEGER	Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.	DECLARE user_list DBMS_STANDARD.ora_name_list_t; number_of_grantees PLS_INTEGER; BEGIN IF (ora_sysevent = 'GRANT') THEN number_of_grantees := ora_grantee(user_list); END IF; END;
ora_instance_num	NUMBER	Instance number.	IF (ora_instance_num = 1) THEN INSERT INTO event_table VALUES ('1'); END IF;
ora_is_alter_column (column_name IN VARCHAR2)	BOOLEAN	Returns true if the specified column is altered.	IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN alter_column := ora_is_alter_column('C'); END IF;
ora_is_creating_nested_table	BOOLEAN	Returns true if the current event is creating a nested table	IF (ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' and ora_is_creating_nested_table) THEN INSERT INTO event_table VALUES ('A nested table is created'); END IF;
ora_is_drop_column (column_name IN VARCHAR2)	BOOLEAN	Returns true if the specified column is dropped.	IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN drop_column := ora_is_drop_column('C'); END IF;
ora_is_servererror	BOOLEAN	Returns TRUE if given error is on error stack, FALSE otherwise.	IF ora_is_servererror(error_number) THEN INSERT INTO event_table VALUES ('Server error!!'); END IF;
ora_login_user	VARCHAR2(30)	Login user name.	SELECT ora_login_user FROM DUAL;
ora_partition_pos	PLS_INTEGER	In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you can insert a PARTITION clause.	-- Retrieve ora_sql_txt into -- sql_text variable first. v_n := ora_partition_pos; v_new_stmt := SUBSTR(sql_text,1,v_n - 1) \|\| ' ' \|\| my_partition_clause \|\| ' ' \|\| SUBSTR(sql_text, v_n));
ora_privilege_list (privilege_list OUT ora_name_list_t)	PLS_INTEGER	Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokees in the OUT parameter; returns the number of privileges in the return value.	DECLARE privelege_list DBMS_STANDARD.ora_name_list_t; number_of_privileges PLS_INTEGER; BEGIN IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN number_of_privileges := ora_privilege_list(privilege_list); END IF; END;
ora_revokee (user_list OUT ora_name_list_t)	PLS_INTEGER	Returns the revokees of a revoke event in the `OUT` parameter; returns the number of revokees in the return value.	DECLARE user_list DBMS_STANDARD.ora_name_list_t; number_of_users PLS_INTEGER; BEGIN IF (ora_sysevent = 'REVOKE') THEN number_of_users := ora_revokee(user_list); END IF; END;
ora_server_error	NUMBER	Given a position (1 for top of stack), it returns the error number at that position on error stack	INSERT INTO event_table VALUES ('top stack error ' \|\| ora_server_error(1));
ora_server_error_depth	PLS_INTEGER	Returns the total number of error messages on the error stack.	n := ora_server_error_depth; -- This value is used with other functions -- such as ora_server_error
ora_server_error_msg (position in pls_integer)	VARCHAR2	Given a position (1 for top of stack), it returns the error message at that position on error stack	INSERT INTO event_table VALUES ('top stack error message' \|\| ora_server_error_msg(1));
ora_server_error_num_params (position in pls_integer)	PLS_INTEGER	Given a position (1 for top of stack), it returns the number of strings that were substituted into the error message using a format like %s.	n := ora_server_error_num_params(1);
ora_server_error_param (position in pls_integer, param in pls_integer)	VARCHAR2	Given a position (1 for top of stack) and a parameter number, returns the matching substitution value (%s, %d, and so on) in the error message.	-- For example, the second %s in a -- message: "Expected %s, found %s" param := ora_server_error_param(1,2);
ora_sql_txt (sql_text out ora_name_list_t)	PLS_INTEGER	Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements. The function return value shows the number of elements are in the PL/SQL table.	--... -- Create table event_table create table event_table (col VARCHAR2(2030)); --... DECLARE sql_text DBMS_STANDARD.ora_name_list_t; n PLS_INTEGER; v_stmt VARCHAR2(2000); BEGIN n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP v_stmt := v_stmt \|\| sql_text(i); END LOOP; INSERT INTO event_table VALUES ('text of triggering statement: ' \|\| v_stmt); END;
ora_sysevent	VARCHAR2(20)	Database event firing the trigger: Event name is same as that in the syntax.	INSERT INTO event_table VALUES (ora_sysevent);
ora_with_grant_option	BOOLEAN	Returns true if the privileges are granted with grant option.	IF (ora_sysevent = 'GRANT' and ora_with_grant_option = TRUE) THEN INSERT INTO event_table VALUES ('with grant option'); END IF;
space_error_info (error_number OUT NUMBER, error_type OUT VARCHAR2, object_owner OUT VARCHAR2, table_space_name OUT VARCHAR2, object_name OUT VARCHAR2, sub_object_name OUT VARCHAR2)	BOOLEAN	Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error.	IF (space_error_info(eno,typ,owner,ts,obj, subobj) = TRUE) THEN DBMS_OUTPUT.PUT_LINE('The object '\|\| obj \|\| ' owned by ' \|\| owner \|\| ' has run out of space.'); END IF;

Database Events

Database events are related to entire instances or schemas, not individual tables or rows. Triggers associated with startup and shutdown events must be defined on the database instance. Triggers associated with on-error and suspend events can be defined on either the database instance or a particular schema.

Table 9-4 Database Events

Event	When Trigger Fires	Conditions	Restrictions	Transaction	Attribute Functions
STARTUP	When the database is opened.	None allowed	No database operations allowed in the trigger. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SHUTDOWN	Just before the server starts the shutdown of an instance. This lets the cartridge shutdown completely. For abnormal instance shutdown, this triiger might not fire.	None allowed	No database operations allowed in the trigger. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
DB_ROLE_CHANGE	When the database is opened for the first time after a role change.	None allowed	Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name
SERVERERROR	When the error eno occurs. If no condition is given, then this trigger fires whenever an error occurs. The trigger does not fire on ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 because they are not true errors or are too serious to continue processing. It also fails to fire on ORA-18 and ORA-20 because a process is not available to connect to the database to record the error.	ERRNO = eno	Depends on the error. Return status ignored.	Starts a separate transaction and commits it after firing the triggers.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations.

The LOGON and LOGOFF events allow simple conditions on UID and USER. All other events allow simple conditions on the type and name of the object, as well as functions like UID and USER.

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot fire later during the same transaction

Table 9-5 Client Events

Event	When Trigger Fires	Attribute Functions
BEFORE ALTER AFTER ALTER	When a catalog object is altered.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_password (for ALTER USER events) ora_is_alter_column (for ALTER TABLE events) ora_is_drop_column (for ALTER TABLE events)
BEFORE DROP AFTER DROP	When a catalog object is dropped.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner
BEFORE ANALYZE AFTER ANALYZE	When an analyze statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS AFTER ASSOCIATE STATISTICS	When an associate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE AUDIT AFTER AUDIT BEFORE NOAUDIT AFTER NOAUDIT	When an audit or noaudit statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name
BEFORE COMMENT AFTER COMMENT	When an object is commented	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE CREATE AFTER CREATE	When a catalog object is created.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events)
BEFORE DDL AFTER DDL	When most SQL DDL statements are issued. Not fired for ALTER DATABASE,CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL subprogram interface, such as creating an advanced queue.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
BEFORE DISASSOCIATE STATISTICS AFTER DISASSOCIATE STATISTICS	When a disassociate statistics statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
BEFORE GRANT AFTER GRANT	When a grant statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privileges
BEFORE LOGOFF	At the start of a user logoff	ora_sysevent ora_login_user ora_instance_num ora_database_name
AFTER LOGON	After a successful logon of a user.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address
BEFORE RENAME AFTER RENAME	When a rename statement is issued.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type
BEFORE REVOKE AFTER REVOKE	When a revoke statement is issued	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privileges
AFTER SUSPEND	After a SQL statement is suspended because of an out-of-space condition. The trigger must correct the condition so the statement can be resumed.	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info
BEFORE TRUNCATE AFTER TRUNCATE	When an object is truncated	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner