In Oracle, what is the difference between a row level trigger and a statement level trigger?

Introduction / Context:
This question examines your understanding of trigger granularity in Oracle Database. Triggers can be defined to fire at different levels, and the two main types are row level and statement level. Knowing when each type fires is crucial for designing correct and efficient business rules that execute automatically in response to table changes.

Given Data / Assumptions:

- The environment is Oracle Database with DML triggers on tables or views.
- We consider BEFORE and AFTER triggers and how many times they fire per statement.
- The key idea is the difference in firing frequency between row level and statement level versions.

Concept / Approach:
In Oracle, a row level trigger is specified with the FOR EACH ROW clause and fires once for each affected row. For example, if an UPDATE statement modifies 100 rows, the row level trigger will execute 100 times. A statement level trigger, which omits FOR EACH ROW, fires only once for the entire statement, regardless of how many rows are affected. This distinction affects performance and the type of logic that can be implemented in each trigger type.

Step-by-Step Solution:
Step 1: Recall that the presence of the FOR EACH ROW clause in the CREATE TRIGGER statement defines a row level trigger. Step 2: Remember that row level triggers have access to the pseudo records :NEW and :OLD for each row. Step 3: Note that a statement level trigger does not include FOR EACH ROW and runs only once per triggering DML statement. Step 4: Evaluate option A and see that it correctly summarizes this behavior by contrasting per row firing and per statement firing. Step 5: Option B incorrectly ties row level triggers only to INSERT and statement level only to UPDATE, which is not how Oracle defines them. Step 6: Option C introduces restrictions about read and write operations that do not exist. Option D claims there is no difference, which is clearly false.

Verification / Alternative check:
You can verify the behavior by creating a simple table and two triggers, one row level and one statement level, and then performing a multi row UPDATE or INSERT. By adding logging statements, you will see that the row level trigger fires once for each row, while the statement level trigger fires only one time. This experiment confirms the description in option A.

Why Other Options Are Wrong:
Option B incorrectly associates trigger type with specific DML commands rather than with granularity. Both row and statement level triggers can be defined for INSERT, UPDATE, or DELETE events. Option C suggests artificial limitations on operations, which are not part of Oracle rules. Option D ignores the FOR EACH ROW clause entirely and is simply wrong in the context of Oracle documentation and real behavior.

Common Pitfalls:
A common mistake is to write complex row level triggers for operations that only need to run once per statement, causing unnecessary performance overhead. Another pitfall is forgetting that statement level triggers do not have access to :NEW and :OLD values, which can lead to compilation errors. Selecting the correct trigger level is important for both correctness and efficiency in production systems.

Final Answer:
A row level trigger fires once for each affected row, whereas a statement level trigger fires once for the entire DML statement, regardless of the number of rows.