Explore topic-wise InterviewSolutions in .

No, INSTEAD OF triggers cannot be USED for each STATEMENT; HOWEVER, It can only be used for each row on a VIEW.

No, INSTEAD OF triggers cannot be used for each statement; however, It can only be used for each row on a view.

Yes, it is POSSIBLE to define and USE an exception within a TRIGGER, as shown in the following code SNIPPET:

Yes, it is possible to define and use an exception within a trigger, as shown in the following code snippet:

The BASE_OBJECT_TYPE column identifies the type of DATABASE object with which the trigger is ASSOCIATED. It SHOWS WHETHER the object of database is a TABLE, VIEW, SCHEMA, or DATABASE.

The BASE_OBJECT_TYPE column identifies the type of database object with which the trigger is associated. It shows whether the object of database is a TABLE, VIEW, SCHEMA, or DATABASE.

No, It is not POSSIBLE to CREATE STARTUP or SHUTDOWN TRIGGERS for ON-SCHEMA.

No, It is not possible to create STARTUP or SHUTDOWN triggers for ON-SCHEMA.

YES, CURSORS can be a PART of CODE in TRIGGER.

Yes, cursors can be a part of code in trigger.

No, a SELECT statement cannot fire a trigger. DML STATEMENTS, such as INSERT, UPDATE, and DELETE, can CAUSE triggers to fire.

No, a SELECT statement cannot fire a trigger. DML statements, such as INSERT, UPDATE, and DELETE, can cause triggers to fire.

No, it is not possible to pass parameters to triggers. However, triggers fired by INSERT and UPDATE statements can REFERENCE new data by using the mew PREFIX. In addition, the triggers fired in RESPONSE to UPDATE and DELETE statements can reference old, MODIFIED, or deleted data using the :old prefix.

No, it is not possible to pass parameters to triggers. However, triggers fired by INSERT and UPDATE statements can reference new data by using the mew prefix. In addition, the triggers fired in response to UPDATE and DELETE statements can reference old, modified, or deleted data using the :old prefix.

YES, USER_TRIGGERS have entries for all triggers that are CREATED in the schema with or without errors.

Yes, USER_TRIGGERS have entries for all triggers that are created in the schema with or without errors.

The USERJERRORS view can be USED to show all the PARSING ERRORS that occur in a TRIGGER during the compilation until they are resolved.

The USERJERRORS view can be used to show all the parsing errors that occur in a trigger during the compilation until they are resolved.

YES, the USER_OBJECTS VIEW has one row entry for each trigger in the SCHEMA.

Yes, the USER_OBJECTS view has one row entry for each trigger in the schema.

No, DDL TRIGGERS are not EXECUTED for DDL statements, which are executed WITHIN the PL/SQL code using the DBMS_SQL package.

No, DDL triggers are not executed for DDL statements, which are executed within the PL/SQL code using the DBMS_SQL package.

TRIGGERS have RESTRICTIONS on the USAGE of large datatypes as they cannot declare or reference the LONG and LONG RAW datatypes and cannot use them even if they form part of the object with which the trigger is associated. Similarly, triggers cannot modify the CLOB and BLOB objects as well; HOWEVER, they can reference them for read-only access.

Triggers have restrictions on the usage of large datatypes as they cannot declare or reference the LONG and LONG RAW datatypes and cannot use them even if they form part of the object with which the trigger is associated. Similarly, triggers cannot modify the CLOB and BLOB objects as well; however, they can reference them for read-only access.

An ALTER TRIGGER statement is USED to recompile, disable, or enable a trigger; WHEREAS, the DROP TRIGGER statement is used to REMOVE the trigger from the database.

An ALTER TRIGGER statement is used to recompile, disable, or enable a trigger; whereas, the DROP TRIGGER statement is used to remove the trigger from the database.

No, A COMMIT statement cannot be EXECUTED as a part of a TRIGGER because it is a TRANSACTION CONTROL statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its CODE.

No, A COMMIT statement cannot be executed as a part of a trigger because it is a Transaction Control statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its code.

No, a view cannot be MUTATING like a table. If an UPDATE STATEMENT fires an INSTEAD OF trigger on a view, the view is not CONSIDERED to be mutating. If the UPDATE statement had been executed on a table, the table WOULD have been considered as mutating.

No, a view cannot be mutating like a table. If an UPDATE statement fires an INSTEAD OF trigger on a view, the view is not considered to be mutating. If the UPDATE statement had been executed on a table, the table would have been considered as mutating.

Yes, triggers have the CAPABILITY of stopping any DML STATEMENT from execution on a table. Any logical BUSINESS rule can be implemented using PL/SQL to block MODIFICATION on table data.

Yes, triggers have the capability of stopping any DML statement from execution on a table. Any logical business rule can be implemented using PL/SQL to block modification on table data.

When a trigger is disabled, it does not MEAN that it is DELETED. The CODE of the trigger is still STORED in the data DICTIONARY but the trigger will not have any effect on the table.

When a trigger is disabled, it does not mean that it is deleted. The code of the trigger is still stored in the data dictionary but the trigger will not have any effect on the table.

The Description COLUMN of the USERJTRIGGERS VIEW combines information from MANY columns to display the TRIGGER header, which INCLUDES the database event.

The Description column of the USERJTRIGGERS view combines information from many columns to display the trigger header, which includes the database event.

It is good to DISABLE TRIGGERS during DATA load operations. This improves the performance of the data loading activities. The data MODIFICATION and manipulation that the trigger would have PERFORMED has to be done manually after the data loading.

It is good to disable triggers during data load operations. This improves the performance of the data loading activities. The data modification and manipulation that the trigger would have performed has to be done manually after the data loading.

Trigger that is EXECUTED when a database event, such as startup, shutdown, or error, occurs is called a database event trigger. It can be used to REFERENCE the attributes of the event and PERFORM system MAINTENANCE functions IMMEDIATELY after the database startup.

Trigger that is executed when a database event, such as startup, shutdown, or error, occurs is called a database event trigger. It can be used to reference the attributes of the event and perform system maintenance functions immediately after the database startup.

Schema-level TRIGGERS are created on schema-level operations, such as create table, alter table, drop table, rename, truncate, and REVOKE. These triggers prevent DDL statements, PROVIDE security, and MONITOR the DDL operations.

Schema-level triggers are created on schema-level operations, such as create table, alter table, drop table, rename, truncate, and revoke. These triggers prevent DDL statements, provide security, and monitor the DDL operations.

A mutating table is a table, which is in the STATE of transition. In other WORDS, it is a table, which is being updated at the TIME of triggering action. If the trigger code queries this table, then a mutating table ERROR occurs, which CAUSES the trigger to view the inconsistent data.

A mutating table is a table, which is in the state of transition. In other words, it is a table, which is being updated at the time of triggering action. If the trigger code queries this table, then a mutating table error occurs, which causes the trigger to view the inconsistent data.

A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) CODE in it, The procedure may be in PL/SQL, C, or Java language. FOLLOWING is an EXAMPLE of the CALL statement:

A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The procedure may be in PL/SQL, C, or Java language. Following is an example of the CALL statement:

Database triggers are based on system EVENTS and can be DEFINED at database or schema level. The various events on which a database TRIGGER can be based are given as follows:

•  DATA definition statement on a database or schema object
•  Logging off or on of a specific user
•  Database shutdown or startup
•  On any specific ERROR that occurs

Database triggers are based on system events and can be defined at database or schema level. The various events on which a database trigger can be based are given as follows:

The performance of a TRIGGER can be improved by using COLUMN names ALONG with the UPDATE clause in the trigger. This will make the trigger fire when that PARTICULAR column is updated and therefore, PREVENTS unnecessary action of trigger when other columns are being updated.

The performance of a trigger can be improved by using column names along with the UPDATE clause in the trigger. This will make the trigger fire when that particular column is updated and therefore, prevents unnecessary action of trigger when other columns are being updated.

• The MAIN DIFFERENCE between database TRIGGER and stored procedure is that the trigger is invoked implicitly and stored procedure is invoked explicitly.
• Transaction Control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, are not allowed within the BODY of a trigger; whereas, these statements can be included in a stored procedure.

The INSTEAD OF triggers are USED in ASSOCIATION with views. The standard table-based triggers cannot be used by views. These triggers inform the database of what actions are to be PERFORMED instead of the actions that invoked the trigger. THEREFORE, the INSTEAD OF triggers can be used to update the underlying tables, which are part of the views. They can be used on both relational views and object views. The INSTEAD OF triggers can only be DEFINED as row-level triggers and not as statement-level triggers.

The INSTEAD OF triggers are used in association with views. The standard table-based triggers cannot be used by views. These triggers inform the database of what actions are to be performed instead of the actions that invoked the trigger. Therefore, the INSTEAD OF triggers can be used to update the underlying tables, which are part of the views. They can be used on both relational views and object views. The INSTEAD OF triggers can only be defined as row-level triggers and not as statement-level triggers.

Triggers can be used to track values for data operations on tables. This is done USING the old and new qualifiers within the TRIGGER code. These TWO clauses help keep track of the data that is being inserted, updated, or deleted in the table; and THEREFORE, facilitate in application auditing of DML statements. The audit trail can be written to a user-defined table and audit records can be generated for both row-level and statement-level triggers.

Triggers can be used to track values for data operations on tables. This is done using the old and new qualifiers within the trigger code. These two clauses help keep track of the data that is being inserted, updated, or deleted in the table; and therefore, facilitate in application auditing of DML statements. The audit trail can be written to a user-defined table and audit records can be generated for both row-level and statement-level triggers.

Yes, it is POSSIBLE to combine various triggering events and create one trigger. Special conditional PREDICATES such as INSERTING, UPDATING, and DELETING are used WITHIN the trigger body to represent the triggering events, as shown in the following CODE snippet:

Yes, it is possible to combine various triggering events and create one trigger. Special conditional predicates such as INSERTING, UPDATING, and DELETING are used within the trigger body to represent the triggering events, as shown in the following code snippet:

• Row-level triggers are ACTIVATED once for each row, which is MODIFIED by a DML statement. For example, consider a table, STUDENT_MARKS_AUDIT, used for the auditing of the STUDENT_MARKS table. A row-level TRIGGER would insert a row in the STUDENT_MARKS_AUDIT table every time any value is modified in any row of the STUDENT_MARKS table.
• Statement-level triggers are executed only once for each DML statement. For example, if there is an INSERT statement, which inserts 100 rows in a STUDENT_MARKS table, then the statement-level trigger would be executed only once on that table; whereas, a row-level trigger would be executed 100 times.
• Row-level triggers are generally used for data auditing applications; whereas, statement-level triggers are used to enforce SECURITY features for the types of actions that may be performed on a table.

Triggers may REQUIRE referencing other TABLES while triggering an event. Therefore, privileges required to reference other tables MUST be granted directly to the owner of the TRIGGER, as the trigger cannot reference other user tables through roles.

For example, consider a trigger, which is used for any audit table. To insert a row in the audit table, the owner of the trigger must have the insert privilege on that audit table.

Triggers may require referencing other tables while triggering an event. Therefore, privileges required to reference other tables must be granted directly to the owner of the trigger, as the trigger cannot reference other user tables through roles.

For example, consider a trigger, which is used for any audit table. To insert a row in the audit table, the owner of the trigger must have the insert privilege on that audit table.

Data manipulation OPERATIONS such as INSERT, UPDATE, and DELETE on a table or a view are the MAIN triggering EVENTS that CAUSE a trigger to execute.

Data manipulation operations such as INSERT, UPDATE, and DELETE on a table or a view are the main triggering events that cause a trigger to execute.

There are FOLLOWING TWO types of triggers:

•  Database triggers are executed implicitly whenever a Data Manipulation Language (DML) statement is carried out on a database table or a Data Definition Language (DDL) statement, such as CREATE OR ALTER, is performed on the database. They may also be executed when a USER or database event occurs, such as a user logs on or a database is shutdown.
•  APPLICATION triggers are executed implicitly whenever a DML event takes place within an application, such as WHEN_NEW_FORM_INSTANCE in the Oracle FORMS application.

There are following two types of triggers:

A USER MUST be able to alter a table to create a trigger on the table. The user must own the table and EITHER have the ALTER TABLE PRIVILEGE on that table or have the ALTER ANY TABLE SYSTEM privilege. In addition, the user must have the CREATE TRIGGER system privilege. User should have the CREATE ANY TRIGGER system privilege to be able to create triggers in any other user account or schema.

A database-level event trigger can be created if the user has the ADMINISTER DATABASE TRIGGER system privilege.

A user must be able to alter a table to create a trigger on the table. The user must own the table and either have the ALTER TABLE privilege on that table or have the ALTER ANY TABLE system privilege. In addition, the user must have the CREATE TRIGGER system privilege. User should have the CREATE ANY TRIGGER system privilege to be able to create triggers in any other user account or schema.

A database-level event trigger can be created if the user has the ADMINISTER DATABASE TRIGGER system privilege.

LOB LOCATORS are values, which specify the location of the large OBJECT.

LOB locators are values, which specify the location of the large object.

Oracle-supplied package CALLED DBMS.J_OB is used to MANIPULATE the LOB OBJECTS.

Oracle-supplied package called DBMS.J_OB is used to manipulate the LOB objects.

The LOB OBJECTS can be stored in-line or out-of-line. The in-line storage means that objects are stored along with the row; WHEREAS, the out-of-line storage means that objects are stored OUTSIDE the row.

The LOB objects can be stored in-line or out-of-line. The in-line storage means that objects are stored along with the row; whereas, the out-of-line storage means that objects are stored outside the row.

The LOB DATATYPE SUPPORTS random access; whereas, the LONG datatype supports only SEQUENTIAL access of objects.

The LOB datatype supports random access; whereas, the LONG datatype supports only sequential access of objects.

The NEXT (<x&GT;) method will RETURN NULL if the last element of the collection is reached.

The NEXT (<x>) method will return NULL if the last element of the collection is reached.

Yes, the EXISTS method can be used for all collection TYPES. It RETURNS a BOOLEAN value, TRUE, if the collection ELEMENT is initialized and FALSE, if the collection element is not initialized.

Yes, the EXISTS method can be used for all collection types. It returns a Boolean value, TRUE, if the collection element is initialized and FALSE, if the collection element is not initialized.

No, COLLECTIONS cannot be COMPARED with BOOLEAN OPERATORS.

No, collections cannot be compared with Boolean operators.

Yes, COLLECTIONS have built-in METHODS, which are used to EXAMINE and manipulate the attributes of the collection, as shown in the FOLLOWING code:

Yes, collections have built-in methods, which are used to examine and manipulate the attributes of the collection, as shown in the following code:

Index-By table is initialized automatically by REFERENCING an element in the COLLECTION; whereas, NESTED tables have to be initialized using a built-in FUNCTION called CONSTRUCTOR.

Index-By table is initialized automatically by referencing an element in the collection; whereas, nested tables have to be initialized using a built-in function called constructor.

No, an uninitialized NESTED table cannot be REFERENCED. It can only be CHECKED for NULLITY.

No, an uninitialized nested table cannot be referenced. It can only be checked for nullity.

No, VARRAY is a COLLECTION and collections cannot be used for RECORD DEFINITION.

No, VARRAY is a collection and collections cannot be used for record definition.

No, RECORDS do not have METHODS.

No, records do not have methods.

Yes, the contents of one record can be COPIED to another by copying EITHER each field USING the assignment operator or WHOLE record using the assignment operator.

Yes, the contents of one record can be copied to another by copying either each field using the assignment operator or whole record using the assignment operator.

No, a CONSTANT VALUE is not ALLOWED in a RECORD.

No, a CONSTANT value is not allowed in a record.

YES, it is possible to define a NOT NULL FIELD in a record so that it will ALWAYS require a value.

Yes, it is possible to define a NOT NULL field in a record so that it will always require a value.

YES, a record definition can have SCALAR DATATYPES or another record as a datatype.

Yes, a record definition can have scalar datatypes or another record as a datatype.