In Oracle PL/SQL, which statement correctly explains the main difference between a trigger and a stored procedure?

Introduction / Context:
This question tests your understanding of two important PL or SQL program units in Oracle: triggers and stored procedures. Although both are blocks of code stored in the database, they are used in different ways. Interviewers frequently ask about the differences between them to evaluate whether candidates understand how business logic is attached either to events or to explicit calls in an application.

Given Data / Assumptions:

- The environment is Oracle Database using PL or SQL.
- Triggers are associated with events on tables or system operations.
- Stored procedures are named blocks of code that can be called directly.

Concept / Approach:
A trigger in Oracle is a special program unit that automatically fires in response to specific events, such as INSERT, UPDATE, or DELETE on a table, or certain system events like logon or startup. A stored procedure is a program unit that is executed only when it is called explicitly, for example with EXEC procedure_name or from within another procedure or application code. This difference in invocation is the fundamental distinguishing factor.

Step-by-Step Solution:
Step 1: Recall that triggers are defined with CREATE TRIGGER and include BEFORE or AFTER clauses and event clauses like INSERT or UPDATE. Step 2: Understand that triggers cannot be called directly by end users in the same way as procedures. They fire automatically as part of DML or system operations. Step 3: Remember that stored procedures are defined with CREATE PROCEDURE and must be invoked explicitly by name with parameters. Step 4: Review option A, which correctly states that triggers execute automatically on events while procedures require explicit invocation. Step 5: Evaluate option B, which reverses the behavior and claims that triggers are called directly while procedures are automatic, which is incorrect. Step 6: Option C says there is no difference, which contradicts both syntax and runtime behavior. Option D incorrectly asserts strict read or write limitations that do not exist.

Verification / Alternative check:
You can verify by thinking about how you test each object. To test a stored procedure, you must execute it explicitly, often in a SQL*Plus or SQL Developer session, passing parameters. To test a trigger, you execute a DML operation on the associated table and observe that the trigger logic fires automatically. This experiment clearly demonstrates the difference in how each program unit runs.

Why Other Options Are Wrong:
Option B is wrong because triggers cannot normally be executed directly by a simple EXEC command; they fire in response to events. Option C is incorrect because there are many differences, including invocation method, association with tables or system events, and limitations on certain actions in triggers. Option D is not true because both triggers and stored procedures can read and modify data, subject to design and permission constraints.

Common Pitfalls:
Developers sometimes misuse triggers to implement large amounts of business logic that might be better placed in stored procedures or application code, making the system harder to debug. Another pitfall is forgetting that triggers run automatically for every qualifying row or statement, which can have performance implications. Understanding when to use triggers versus procedures is an important design skill.

Final Answer:
A trigger executes automatically in response to defined database events, while a stored procedure runs only when it is invoked explicitly by a user, application, or another PL or SQL block.