Row-Level Triggers FAQS

1. What is a row-level trigger in Oracle?

A row-level trigger in Oracle is a trigger that fires once for each row affected by a Data Manipulation Language (DML) operation, such as INSERT, UPDATE, or DELETE. It is useful when you need to perform actions on a per-row basis, such as auditing, data validation, or enforcing business rules.

2. How does a row-level trigger differ from a statement-level trigger?

·        Row-Level Trigger: Executes once for each row affected by the DML operation. It allows access to both old and new values for UPDATE and DELETE operations.

·        Statement-Level Trigger: Executes once for the entire SQL statement, regardless of how many rows are affected. It does not have access to individual row data.

3. Can a row-level trigger modify the data it is triggered on?

Yes, a row-level trigger can modify the row data using the :NEW and :OLD values. For example, in an INSERT trigger, the :NEW value refers to the data being inserted, and in an UPDATE trigger, both the :NEW and :OLD values are available to compare before and after values.

4. What are :NEW and :OLD references in row-level triggers?

·        :NEW: Represents the new value of a column in an INSERT or UPDATE operation. In UPDATE, it refers to the value after the modification.

·        :OLD: Represents the old value of a column in an UPDATE or DELETE operation. In UPDATE, it refers to the value before the modification.

5. Can I prevent data changes using a row-level trigger?

Yes, you can prevent data changes by raising exceptions in the trigger body. For example, you can raise an exception in an UPDATE or INSERT trigger to prevent a row from being inserted or updated if certain conditions are met.

6. Can a row-level trigger be used for auditing purposes?

Yes, row-level triggers are commonly used for auditing purposes. They can log the old and new values of rows before they are inserted, updated, or deleted, providing an audit trail of changes made to the database.

7. How do row-level triggers affect performance?

Row-level triggers are executed for each row affected by a DML operation. Therefore, if the DML operation affects many rows, the trigger will execute multiple times, which could lead to performance degradation. To minimize overhead, it's best to keep the trigger logic simple and efficient.

8. What is a "mutating table" error, and how do I avoid it?

A mutating table error occurs when a trigger attempts to query or modify the same table that caused it to fire. This leads to a conflict because the table is in a "mutating" state during the DML operation. To avoid this, you can use compound triggers or temporary tables to store intermediate data, or use a statement-level trigger to handle the logic.

9. Can I use a row-level trigger to automatically generate default values?

Yes, row-level triggers can be used to automatically assign default values to columns when an INSERT operation is performed. For example, you can set the current date in a hire_date column if it is not provided.

10. How do I disable or drop a row-level trigger?

·        To disable a row-level trigger:

·        ALTER TRIGGER trigger_name DISABLE;

·        To enable a row-level trigger:

·        ALTER TRIGGER trigger_name ENABLE;

·        To drop a row-level trigger:

·        DROP TRIGGER trigger_name;

11. What is a compound trigger, and how is it related to row-level triggers?

A compound trigger allows you to combine both statement-level and row-level logic within a single trigger. It helps in situations where you need to handle mutating table errors by separating row-level actions and statement-level actions in the same trigger, which is not possible with traditional row-level triggers.

12. Can row-level triggers be used with views?

Yes, row-level triggers can be created for INSTEAD OF operations on views. These triggers are used to specify the action that should occur when a INSERT, UPDATE, or DELETE operation is performed on a view.

13. What types of actions can be performed in a row-level trigger?

In a row-level trigger, you can perform various actions like:

·        Modifying row data using :NEW and :OLD references.

·        Logging changes to an audit table.

·        Enforcing business rules, such as checking for valid data before inserting or updating.

·        Raising exceptions to prevent certain changes.

14. Can a row-level trigger be used to update another table?

Yes, row-level triggers can be used to update other tables, for example, to maintain consistency across related tables. However, care should be taken to avoid mutating table errors if you're trying to update the same table that fired the trigger.

15. Can a row-level trigger be used for DELETE operations?

Yes, row-level triggers can be used for DELETE operations. In this case, the trigger is executed once for each row being deleted, and you can access the old values of the row using the :OLD reference.

16. Can I use a row-level trigger to enforce constraints on data?

Yes, row-level triggers can be used to enforce additional constraints or business rules that are not part of the database's built-in constraints. For example, you could use a trigger to ensure that the salary of an employee does not exceed a certain limit before performing an UPDATE.