In PL/SQL, what is a database trigger and what are the main types of triggers based on timing and level (row or statement)?

Introduction / Context:
Database triggers are a powerful feature in PL/SQL that allow code to execute automatically when certain events occur in the database. They can enforce business rules, maintain audit trails, or synchronize related data. Understanding what a trigger is and how different types are classified by timing and level is an important part of PL/SQL and database design knowledge.

Given Data / Assumptions:

• The platform is Oracle Database with PL/SQL support.
• Triggers are associated with tables or views and respond to data manipulation events.
• We want to identify both the definition of a trigger and the typical types used.
• We assume basic familiarity with DML statements such as INSERT, UPDATE, and DELETE.

Concept / Approach:
A trigger is a stored PL/SQL program unit that is associated with a table, view, schema, or database and is executed automatically when a specified event occurs. Common triggering events are INSERT, UPDATE, and DELETE on a table. Triggers are classified by timing as BEFORE or AFTER, indicating whether they run before or after the triggering statement is executed. They are also classified by level as row level, which executes once for each affected row, or statement level, which executes once per triggering SQL statement regardless of the number of rows affected.

Step-by-Step Solution:
1. Recognize that triggers are stored PL/SQL code that the database engine executes automatically, not code that applications call explicitly. 2. Understand that the trigger definition specifies the table or object it is attached to, as well as the triggering events such as INSERT, UPDATE, or DELETE. 3. Identify timing types: BEFORE triggers run before the DML operation modifies data, and AFTER triggers run after the modification. 4. Identify level types: row level triggers execute once per row and can reference row specific values, while statement level triggers execute once per statement and cannot access individual row values in the same way. 5. Select the option that accurately defines a trigger and mentions BEFORE, AFTER, row level, and statement level as the main categories.

Verification / Alternative check:
You can verify trigger behaviour by creating simple BEFORE ROW and AFTER STATEMENT triggers on a test table. For example, a BEFORE ROW trigger can log old and new values for each row that changes, while an AFTER STATEMENT trigger can insert a summary record into an audit table after a batch update. By inserting or updating data and examining the results, you can confirm that triggers fire automatically according to their timing and level settings.

Why Other Options Are Wrong:

• Option B is wrong because triggers run on the database server, not in the browser, and they are written in PL/SQL, not in client side script languages.
• Option C is wrong because an index is a data structure for fast lookups, not an executable program that responds to events.
• Option D is wrong because a view is a virtual table defined by a query and does not contain procedural code that automatically executes.

Common Pitfalls:
Poorly designed triggers can introduce complexity, hidden side effects, and performance issues. A common pitfall is placing heavy business logic in triggers that make simple DML operations unexpectedly costly. Another mistake is creating mutually dependent triggers that cause cascading updates and possible recursion. Developers should use triggers responsibly, keeping them focused on tasks that naturally belong in the database layer, such as audit logging and simple data integrity checks that cannot be enforced with constraints alone.

Final Answer:
The correct explanation is A trigger is a stored PL/SQL program that fires automatically in response to events such as INSERT, UPDATE, or DELETE, and triggers can be BEFORE or AFTER and defined as row level or statement level, because this definition covers both what a trigger is and the main ways in which triggers are classified in PL/SQL.