In Oracle Database, what is the maximum number of row and statement level triggers that can be defined on a single table for the basic INSERT, UPDATE, and DELETE events?

Introduction / Context:
Database triggers are special stored procedures that run automatically in response to events such as INSERT, UPDATE, or DELETE on a table. In Oracle Database, older exam questions often ask about the maximum number of triggers that can be associated with a single table for the basic row and statement events. This tests your understanding of how many different combinations of BEFORE, AFTER, and DML operation types are supported by Oracle's trigger framework in a traditional configuration.

Given Data / Assumptions:

• The database is Oracle.
• We are considering row level and statement level triggers.
• The DML operations of interest are INSERT, UPDATE, and DELETE.
• The classic trigger types include BEFORE and AFTER timing points for each operation and level.
• We are not including INSTEAD OF triggers on views or compound triggers in this count.

Concept / Approach:
For a traditional table in Oracle, triggers can be defined for each combination of timing (BEFORE or AFTER), operation (INSERT, UPDATE, DELETE), and level (ROW or STATEMENT). For each DML operation, you can have BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT. That gives four distinct trigger points per operation. With three operations (INSERT, UPDATE, DELETE), you multiply 3 by 4 to get 12 possible triggers. Classic exam questions refer to this as the maximum number of basic triggers that can be applied to a table in this model.

Step-by-Step Solution:
Step 1: List the DML operations: INSERT, UPDATE, and DELETE, for a total of 3 operations. Step 2: For each operation, identify the timing points: BEFORE and AFTER. Step 3: For each timing, identify the level: STATEMENT level (once per statement) and ROW level (once per affected row). Step 4: Count the trigger points for one operation: BEFORE STATEMENT, BEFORE ROW, AFTER ROW, AFTER STATEMENT, which equals 4. Step 5: Multiply by the number of operations: 4 trigger points per operation * 3 operations = 12 distinct triggers that can be defined for a single table.

Verification / Alternative check:
You can verify this reasoning by writing out the full list of triggers for a sample table. For example, you could theoretically define BEFORE INSERT ON t FOR EACH ROW, BEFORE INSERT ON t, AFTER INSERT ON t FOR EACH ROW, AFTER INSERT ON t, and similarly for UPDATE and DELETE. Counting them yields 4 per operation and 12 total. While modern Oracle versions allow more complex trigger combinations and multiple triggers of the same type, the classic theoretical maximum of distinct timing and level combinations remains 12. This is the figure usually expected in exam style questions.

Why Other Options Are Wrong:
Option 8 triggers per table: This would correspond to fewer combinations and leaves out some valid BEFORE or AFTER row or statement trigger types. Option 9 triggers per table: There is no straightforward way to derive 9 from the combination of 3 operations, 2 timings, and 2 levels. Option 14 triggers per table: This overstates the number of distinct BEFORE or AFTER row and statement trigger combinations for the three DML operations.

Common Pitfalls:
A frequent source of confusion is mixing up the number of trigger types with the number of trigger objects that can actually exist in modern Oracle versions, where multiple triggers of the same type are allowed. Exam questions of this style, however, focus on the theoretical matrix of BEFORE or AFTER, STATEMENT or ROW, and INSERT, UPDATE, or DELETE. Another pitfall is forgetting that UPDATE also has its own set of triggers, leading students to count only INSERT and DELETE. Careful enumeration of all timing and level combinations avoids these mistakes.

Final Answer:
For the basic BEFORE or AFTER, row and statement triggers on INSERT, UPDATE, and DELETE, the maximum number on a single Oracle table is 12 triggers per table.