PL/SQL Trigger Types: Row-Level vs Statement-Level Quiz Quiz

Enhance your understanding of PL/SQL triggers by distinguishing between row-level and statement-level triggers. This quiz tests essential concepts, practical scenarios, and definitions related to trigger types in PL/SQL databases.

1. Identifying Trigger Types

   Which type of PL/SQL trigger is executed once for each row affected by a DML statement, such as an UPDATE on five rows?

   1. Statement-level trigger
   2. Row-level trigger
   3. Line-level trigger
   4. Block-level trigger

   Explanation: A row-level trigger fires once for each row affected by the DML statement, so an UPDATE on five rows invokes the trigger five times. Statement-level triggers execute just once per triggering statement, regardless of the number of affected rows. Line-level and block-level triggers do not exist in standard PL/SQL terminology.

2. Understanding Trigger Execution

   If you want a trigger to execute just once after a DELETE statement, regardless of how many rows were deleted, which trigger type should you use?

   1. Statement-level trigger
   2. Table-level trigger
   3. Cursor-level trigger
   4. Row-level trigger

   Explanation: Statement-level triggers are designed to run a single time for each triggering DML statement, such as after a DELETE command. Row-level triggers would execute for every deleted row. Cursor-level triggers are not a standard trigger type in PL/SQL, and table-level is an incorrect term for this context.

3. Syntax Recognition

   Which keyword must be included in a trigger definition to indicate a row-level trigger in PL/SQL?

   1. FOR EACH STATEMENT
   2. FOR EACH FIELD
   3. FOR EVERY ROWS
   4. FOR EACH ROW

   Explanation: The keyword 'FOR EACH ROW' is required to specify a row-level trigger, ensuring the trigger executes for every affected row. 'FOR EACH STATEMENT' is not valid PL/SQL syntax for trigger definition. 'FOR EVERY ROWS' and 'FOR EACH FIELD' are incorrect and do not exist in PL/SQL.

4. Accessing Pseudo-records

   Within which type of trigger can you access the :NEW and :OLD pseudo-records to reference column values before and after DML changes?

   1. Transaction-level trigger
   2. Statement-level trigger
   3. Row-level trigger
   4. Event-level trigger

   Explanation: :NEW and :OLD pseudo-records are accessible only within row-level triggers, providing old and new values for a particular row. Statement-level triggers do not have access to these pseudo-records because they do not execute per row. Event-level and transaction-level are not recognized trigger types in PL/SQL.

5. Default Trigger Level

   If neither 'FOR EACH ROW' nor any similar clause is used when creating a trigger, what is the default type of trigger created in PL/SQL?

   1. Object-level trigger
   2. Statement-level trigger
   3. Row-level trigger
   4. After-level trigger

   Explanation: By default, PL/SQL creates a statement-level trigger when 'FOR EACH ROW' is omitted. Row-level triggers require explicit declaration. There are no standard 'after-level' or 'object-level' triggers in PL/SQL, so those options are incorrect.

6. Scenario-Based Understanding

   A trigger is meant to log an entry every time any records are inserted into a table, but not for each individual record. Which trigger should be implemented?

   1. Cursor-level trigger
   2. Statement-level trigger
   3. Field-level trigger
   4. Row-level trigger

   Explanation: A statement-level trigger logs once per INSERT statement, not per row, making it suitable for this purpose. Row-level triggers would make one log per new record. Cursor-level and field-level triggers do not exist in PL/SQL.

7. Multiple Trigger Types

   Can both a row-level trigger and a statement-level trigger be created on the same table for the same DML event in PL/SQL?

   1. Yes, both can exist independently
   2. Only row-level triggers are allowed
   3. Only statement-level triggers are allowed
   4. No, only one trigger type can exist per DML event

   Explanation: PL/SQL allows both row-level and statement-level triggers to be defined on the same table and for the same DML event, acting independently. The platform does not restrict you to only one trigger type per event. Options stating only one trigger type can exist are incorrect.

8. Choosing the Efficient Trigger Type

   For auditing user actions at the table level without tracking each row change, which trigger type is generally more efficient?

   1. Row-level trigger
   2. Record-level trigger
   3. Session-level trigger
   4. Statement-level trigger

   Explanation: Statement-level triggers are more efficient for table-level auditing because they execute once per statement, avoiding multiple executions. Row-level triggers would unnecessarily fire for each changed row, reducing performance. Record-level and session-level triggers do not exist as PL/SQL options.

9. Determining Trigger Use Case

   You need to validate data before each individual row is inserted, such as checking if a salary is above a certain value. Which trigger type is most appropriate?

   1. Batch-level trigger
   2. Row-level trigger
   3. Statement-level trigger
   4. System-level trigger

   Explanation: Row-level triggers are necessary when validation is needed for every row, like checking each salary. Statement-level triggers do not process individual row data. Batch-level and system-level triggers are not standard terminology in PL/SQL.

10. After vs Before Trigger Limitations

    Which statement correctly describes a limitation of statement-level triggers compared to row-level triggers in PL/SQL?

    1. Statement-level triggers can only be used for SELECT statements
    2. Statement-level triggers cannot execute after a DML event
    3. Statement-level triggers cannot access the :NEW or :OLD pseudo-records
    4. Statement-level triggers cannot be created in PL/SQL

    Explanation: Statement-level triggers in PL/SQL cannot access :NEW or :OLD pseudo-records since they do not operate on individual rows. They can execute after DML events and are fully supported in PL/SQL. Triggers cannot be created for SELECT statements, regardless of trigger type.