PL/SQL Triggers for Auditing and Logging: Fundamentals Quiz Quiz

Enhance your understanding of PL/SQL database triggers for auditing and logging, focusing on syntax, best practices, and real-world scenarios in database management. Assess your skills with beginner-friendly questions tailored for those interested in efficient trigger design and database change tracking.

1. Basic Trigger Purpose

   Which primary purpose do PL/SQL triggers serve in database auditing and logging scenarios?

   1. They automatically log changes to database tables
   2. They schedule backup jobs
   3. They parse JSON data from the web
   4. They enforce foreign key constraints

   Explanation: Triggers are commonly used to log changes made to database tables for auditing purposes, ensuring a record is kept of data modifications. Enforcing foreign key constraints is managed by database schema design, not triggers. Scheduling backups and parsing JSON data are external tasks and not the core function of triggers in auditing contexts.

2. Trigger Timing

   If you want to record every new record added to an 'employees' table, which trigger type should you use?

   1. BEFORE DELETE
   2. AFTER INSERT
   3. INSTEAD OF SELECT
   4. AFTER UPDATE

   Explanation: An AFTER INSERT trigger fires only after a new record is successfully added, making it ideal for logging insert actions. AFTER UPDATE triggers after data changes, not insertions. BEFORE DELETE is used before records are removed, and INSTEAD OF SELECT does not exist in PL/SQL; the common INSTEAD OF trigger is for views and not SELECT operations.

3. Trigger Syntax Components

   Which clause in a PL/SQL trigger defines the specific table the trigger is associated with?

   1. ON table_name
   2. FOR EACH ROW
   3. WHEN condition
   4. BEGIN

   Explanation: The ON table_name clause specifies which table the trigger monitors. FOR EACH ROW indicates the trigger is row-level, not table-leveled, but does not define the table. WHEN condition provides a conditional execution, and BEGIN signals the start of the PL/SQL block within the trigger.

4. Audit Table Structure

   When designing an audit table for logging changes, which column is best to include for tracking the action performed?

   1. salary
   2. date_of_birth
   3. action_type
   4. employee_address

   Explanation: An action_type column is essential to specify whether the operation was an INSERT, UPDATE, or DELETE, which is vital for auditing. Employee_address, salary, and date_of_birth may store business data but do not capture the action performed.

5. NEW and OLD References

   In a row-level trigger, which keyword allows you to access the updated value of a column during an UPDATE operation?

   1. CURRENT
   2. INSERTED
   3. NEW
   4. OLD

   Explanation: NEW accesses the modified column values after an UPDATE or INSERT. OLD is used to refer to column values before the DML action. INSERTED is not a keyword in PL/SQL triggers, though it exists in other systems. CURRENT is not a valid reference for column values in PL/SQL triggers.

6. Logging User Actions

   Which built-in PL/SQL function retrieves the name of the current user who performed a DML operation for audit logging?

   1. CURRENT_USER
   2. USER_NAME
   3. SESSION_USER
   4. SYSDATE

   Explanation: USER is the built-in function that returns the name of the user executing the DML command, useful for audit trails. CURRENT_USER and SESSION_USER may be seen in other database systems, but are not standard in PL/SQL triggers. SYSDATE retrieves the current date and time, not the user name.

7. BEFORE vs AFTER Triggers

   For logging the final values after a data change to a 'products' table, which trigger timing is most appropriate?

   1. BEFORE UPDATE
   2. BEFORE DELETE
   3. BEFORE INSERT
   4. AFTER UPDATE

   Explanation: AFTER UPDATE triggers execute after new values have been stored in the table, allowing accurate logging of the updated values. BEFORE INSERT and BEFORE UPDATE fire before the changes are made, and BEFORE DELETE occurs prior to row deletion, making them less suitable for logging final stored values.

8. Preventing Recursive Triggers

   Why should you avoid performing DML operations on the same table inside its own trigger?

   1. To prevent recursive trigger firing
   2. To increase system speed
   3. To bypass transaction control
   4. To allow simultaneous updates

   Explanation: Performing DML within a trigger on the same table can cause the trigger to fire repeatedly, leading to recursion and possible stack overflow errors. While speed, transaction control, and simultaneous updates are important considerations, the primary concern here is recursion, not the others.

9. Mutating Table Error

   If you receive a 'mutating table' error when logging changes in a row-level trigger, what is the main cause?

   1. Creating indexes
   2. Reading or writing to the same table within the trigger
   3. Dropping columns
   4. Granting privileges

   Explanation: The mutating table error occurs if a row-level trigger tries to read from or modify the table that is currently being changed, violating transactional consistency. Dropping columns, granting privileges, and creating indexes are unrelated to this specific trigger error.

10. Storing Audit Records

    Where should audit information generally be stored to ensure reliable historical tracking in a PL/SQL logging solution?

    1. A temporary table
    2. A dedicated audit table
    3. The same table being audited
    4. Transaction logs only

    Explanation: A separate audit table is recommended to accurately store and manage historical action records without interfering with the main data. Keeping audit info in the same table could clutter data and risks overwriting. Temporary tables lose data after sessions end, and transaction logs are not meant for user-level auditing.