DML Triggers FAQS

1. What is a DML trigger in Oracle?

A DML trigger (Data Manipulation Language trigger) is a set of PL/SQL code that automatically executes in response to an INSERT, UPDATE, or DELETE operation on a table or view. DML triggers are used to enforce data integrity, apply business logic, or track changes.

2. What are the types of DML triggers in Oracle?

Oracle supports three main types of DML triggers:

• BEFORE Trigger: Executes before the DML operation.
• AFTER Trigger: Executes after the DML operation.
• INSTEAD OF Trigger: Typically used with views, this replaces the DML operation on the view with custom logic.

3. What is the difference between a BEFORE and AFTER trigger?

• BEFORE Trigger: Executes before the DML operation is carried out, giving you a chance to modify the data or raise an exception if necessary.
• AFTER Trigger: Executes after the DML operation is completed, allowing you to perform actions such as logging changes or maintaining audit trails.

4. What is the difference between row-level and statement-level triggers?

• Row-Level Trigger: Executes once for each row affected by the DML operation. For example, if an UPDATE affects 10 rows, the trigger will execute 10 times (once for each row).
• Statement-Level Trigger: Executes once for the entire DML operation, regardless of how many rows are affected. For example, an INSERT that affects 100 rows will only fire the trigger once.

5. What are :NEW and :OLD in DML triggers?

• :NEW: Refers to the new values of the row being inserted or updated. In an INSERT operation, it represents the row being inserted. In an UPDATE operation, it holds the updated values.
• :OLD: Refers to the old values of the row before it was updated or deleted. It is used in UPDATE and DELETE triggers to reference the state of the row prior to the operation.

6. How do I prevent certain DML operations using triggers?

You can prevent DML operations like DELETE or UPDATE by raising exceptions in the trigger's body. For example, you can prevent the deletion of an employee if their salary exceeds a certain amount:

CREATE OR REPLACE TRIGGER prevent_delete

BEFORE DELETE ON employees

FOR EACH ROW

BEGIN

   IF :OLD.salary > 100000 THEN

      RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employee with salary > 100,000');

   END IF;

END;

/

7. Can I have a trigger for multiple DML operations (INSERT, UPDATE, DELETE)?

Yes, you can create a trigger that responds to multiple DML operations by using OR in the trigger definition. For example, you can write a trigger that fires after any of INSERT, UPDATE, or DELETE:

CREATE OR REPLACE TRIGGER multi_dml_trigger

AFTER INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

BEGIN

   IF INSERTING THEN

      -- Handle insert

   ELSIF UPDATING THEN

      -- Handle update

   ELSIF DELETING THEN

      -- Handle delete

   END IF;

END;

/

8. What happens if a trigger raises an exception?

If a trigger raises an exception, the DML operation that caused the trigger to fire will be rolled back, and an error will be returned to the user. For example, if a BEFORE INSERT trigger raises an exception, the insert will not proceed.

9. Can I update other tables in a trigger?

Yes, you can perform any DML operation (e.g., INSERT, UPDATE, DELETE) inside the trigger, including updating other tables. However, be cautious because doing so may lead to unintended consequences like mutating table errors.

10. What is a mutating table error?

A mutating table error occurs when a trigger tries to read or modify the table that fired it during the execution of the trigger. For example, if an AFTER INSERT trigger on the employees table attempts to query or update the employees table, a mutating table error is raised. This can be avoided by using statement-level triggers or by storing the changes in a temporary table and then performing the actions after the DML operation.

11. How do I handle performance issues with triggers?

• Keep Trigger Logic Lightweight: Avoid complex and long-running queries in triggers.
• Use Statement-Level Triggers for Bulk Operations: When dealing with large volumes of data, statement-level triggers execute only once for the whole DML statement, rather than for each affected row.
• Avoid Nested Triggers: Ensure that triggers do not recursively call other triggers, as this can lead to performance bottlenecks.

12. Can triggers affect transactional integrity?

Yes, since triggers are part of the same transaction as the DML operation, any changes made by a trigger are committed or rolled back together with the original DML operation. If an exception is raised in the trigger, the entire transaction will be rolled back.

13. How can I track changes to data using triggers?

You can use triggers to log changes to a table by inserting records into an audit table. For example, a trigger can be created to log all INSERT, UPDATE, and DELETE operations to a separate audit table:

CREATE OR REPLACE TRIGGER audit_trigger

AFTER INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

BEGIN

   INSERT INTO audit_table (operation, emp_id, old_salary, new_salary, change_time)

   VALUES (

      CASE

         WHEN INSERTING THEN 'INSERT'

         WHEN UPDATING THEN 'UPDATE'

         WHEN DELETING THEN 'DELETE'

      END,

      :NEW.emp_id,

      :OLD.salary,

      :NEW.salary,

      SYSDATE

   );

END;

/

14. What is the WHEN clause in a trigger?

The WHEN clause allows you to specify a condition that must be met for the trigger to fire. If the condition evaluates to TRUE, the trigger action will be executed; otherwise, it will not.

CREATE OR REPLACE TRIGGER check_salary_before_insert

BEFORE INSERT ON employees

FOR EACH ROW

WHEN (NEW.salary > 50000)

BEGIN

   -- Trigger action

END;

/

15. Can I disable or drop a trigger in Oracle?

Yes, you can disable or drop a trigger using the following commands:

• Disable a Trigger:

ALTER TRIGGER trigger_name DISABLE;

• Enable a Trigger:

ALTER TRIGGER trigger_name ENABLE;

• Drop a Trigger:

DROP TRIGGER trigger_name;