Introduction to Triggers FAQS

1. What is an Oracle trigger?

An Oracle trigger is a stored PL/SQL block that automatically executes (or "fires") in response to specific events such as INSERT, UPDATE, or DELETE operations on a table or view. Triggers are used to enforce business rules, ensure data integrity, or perform auditing tasks.

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

• DML Triggers: These are fired by INSERT, UPDATE, or DELETE operations on a table or view.
• BEFORE Trigger: Fired before the DML operation is executed.
• AFTER Trigger: Fired after the DML operation is executed.
• INSTEAD OF Trigger: Replaces the DML operation, typically used on views.
• DDL Triggers: Fired in response to Data Definition Language commands (like CREATE, ALTER, or DROP).
• LOGON/LOGOFF Triggers: Fired when a user logs in or logs off the database.
• Compound Triggers: Allow multiple actions (BEFORE and AFTER) to be combined in a single trigger, which helps prevent mutating table errors.

3. What is the difference between BEFORE and AFTER triggers?

• BEFORE Trigger: Executes before the DML operation (e.g., INSERT, UPDATE, DELETE). Useful for validating or modifying data before it is committed.
• AFTER Trigger: Executes after the DML operation has completed. It's commonly used for auditing or logging changes.

4. What is a row-level trigger and a statement-level trigger?

• Row-level Trigger: Fired for each row affected by the DML operation. This is specified with the FOR EACH ROW clause.
• Example: If 100 rows are updated, the trigger will fire 100 times.
• Statement-level Trigger: Fired once for the entire DML statement, regardless of how many rows are affected. These triggers are created without the FOR EACH ROW clause.

5. How do I reference old and new values in triggers?

• :NEW: Refers to the new value of a column during an INSERT or UPDATE operation.
• :OLD: Refers to the old value of a column during an UPDATE or DELETE operation.

Example:

-- In an UPDATE trigger

:OLD.salary  -- The old salary value (before the update)

:NEW.salary  -- The new salary value (after the update)

6. Can a trigger call other triggers?

No, Oracle does not allow one trigger to directly call another trigger. However, a trigger can call stored procedures or functions, which in turn may modify data and activate other triggers.

7. What are mutating table errors, and how can I avoid them?

A mutating table error occurs when a trigger tries to modify the same table that fired it (for example, updating a table while a trigger is executing an UPDATE on the same table). To avoid this, you can:

• Use compound triggers to combine different actions into one trigger.
• Use temporary tables or collections to store interim data and avoid direct modifications to the triggering table.

 

8. Can triggers be used to enforce business rules?

Yes, triggers are often used to enforce business rules by ensuring that certain conditions are met before or after a DML operation. For example, a trigger can prevent inserting a negative salary or ensure that stock levels are updated correctly after a sale.

9. How do I disable or enable a trigger in Oracle?

• Disable a trigger:

·        ALTER TRIGGER trigger_name DISABLE;

• Enable a trigger:

·        ALTER TRIGGER trigger_name ENABLE;

10. How can I drop a trigger in Oracle?

To remove a trigger from a table, you can use the DROP TRIGGER command:

DROP TRIGGER trigger_name;

11. What is an INSTEAD OF trigger?

An INSTEAD OF trigger is a special type of trigger that is used to replace the normal INSERT, UPDATE, or DELETE operation with custom logic. This is particularly useful for views, where you can define how updates to a view should be handled, even though a view may not support direct DML operations.

Example:

CREATE OR REPLACE TRIGGER update_employee_view

INSTEAD OF UPDATE ON employee_view

FOR EACH ROW

BEGIN

    UPDATE employees

    SET salary = :NEW.salary

    WHERE employee_id = :OLD.employee_id;

END;

 

12. What happens if I insert data that violates a trigger condition?

If the trigger has a RAISE_APPLICATION_ERROR or any kind of error handling (such as EXCEPTION blocks), the DML operation will fail and the error will be raised. For example, if an INSERT operation violates a BEFORE INSERT trigger's validation, the transaction will be rolled back, and the error message will be displayed.

13. Can triggers be used for auditing?

Yes, triggers are often used for auditing purposes. For example, you can create a trigger that logs every change made to a table (such as updates to the salary column in an employees table) into an audit table.

Example:

CREATE OR REPLACE TRIGGER audit_employee_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

    INSERT INTO employee_audit (emp_id, old_salary, new_salary, change_date)

    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);

END;

14. Can triggers cause performance issues?

Yes, triggers can introduce some performance overhead because they are executed automatically with each DML operation. If a trigger contains complex logic, it can slow down data manipulation operations. It is important to carefully design triggers, especially in high-transaction environments.

 

15. Can I use triggers on views?

Yes, you can use triggers on views, but only INSTEAD OF triggers are supported for views. An INSTEAD OF trigger replaces the standard INSERT, UPDATE, or DELETE operations on a view with custom logic that manipulates the underlying tables.

16. How do I debug a trigger in Oracle?

To debug a trigger, you can:

• Use DBMS_OUTPUT.PUT_LINE to print values for debugging purposes.
• Check the Oracle alert log for errors.
• Use exception handling in the trigger to catch and report issues.

Example:

BEGIN

    DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :OLD.salary);

    DBMS_OUTPUT.PUT_LINE('New Salary: ' || :NEW.salary);

END;

17. How do triggers affect transaction control?

Triggers execute within the same transaction as the DML operation that fired them. If a trigger encounters an error or raises an exception, the entire transaction will be rolled back, including the DML operation that triggered the event. This behavior ensures data integrity.

18. What is the difference between triggers and stored procedures?

• Triggers are automatically executed in response to specific events (like DML operations), and they are often used for enforcing business rules or auditing.
• Stored procedures are manually executed blocks of code that can be called from other SQL statements or PL/SQL blocks. They are typically used to encapsulate business logic that must be explicitly invoked.