Purpose of Triggers FAQS

1. What is an Oracle trigger?

An Oracle trigger is a stored procedure that automatically executes (or "fires") when a specific event (such as an INSERT, UPDATE, or DELETE) occurs on a table or view. Triggers are used to enforce business rules, maintain data integrity, and automate tasks in the database.

2. What are the types of triggers in Oracle?

Oracle supports several types of triggers:

• DML Triggers (Data Manipulation Language): Triggered by data changes (INSERT, UPDATE, DELETE).
• BEFORE Trigger: Executes before the DML operation.
• AFTER Trigger: Executes after the DML operation.
• INSTEAD OF Trigger: Replaces the DML operation, often used with views.
• DDL Triggers (Data Definition Language): Triggered by schema changes like CREATE, ALTER, or DROP.
• LOGON / LOGOFF Triggers: Triggered when a user logs in or out of the database.
• Compound Triggers: A special type of trigger that can handle multiple timing points to avoid mutating table errors.

3. What are the main purposes of using triggers?

Triggers are used for several purposes in Oracle, including:

1. Data Integrity: Ensuring that data meets specific business rules and constraints.
2. Business Logic Enforcement: Automating complex business rules directly in the database.
3. Auditing: Keeping track of changes to data for compliance or auditing purposes.
4. Validation: Enforcing complex validation rules on data.
5. Preventing Invalid Transactions: Preventing certain operations (like deletions) when certain conditions are met.
6. Cascading Changes: Automatically updating or deleting related records in other tables.
7. Automating System Tasks: Performing automated tasks such as sending notifications or updating timestamp fields.

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

• BEFORE Trigger: Executes before the DML operation (INSERT, UPDATE, DELETE). It allows you to modify the data before it is inserted/updated into the table.
• Example: You might use a BEFORE INSERT trigger to set default values for a new row.
• AFTER Trigger: Executes after the DML operation. It is useful when you need to take actions after the data has been committed to the table.
• Example: An AFTER DELETE trigger might log deleted data into an audit table.

5. What is an INSTEAD OF trigger?

An INSTEAD OF trigger is used to replace a DML operation (INSERT, UPDATE, DELETE) with custom logic. This is especially useful for views, where direct modifications are not allowed. The INSTEAD OF trigger intercepts the DML operation and provides an alternative implementation.

• Example: You can use an INSTEAD OF trigger on a view to update the underlying base tables instead of directly modifying the view itself.

6. What is a mutating table error, and how can it be avoided?

A mutating table error occurs when a trigger tries to modify or query the table that fired the trigger. For example, in an AFTER UPDATE trigger, if you attempt to update the same table, Oracle will throw an error because it’s trying to modify the table while it is in the middle of an operation. Solution: To avoid this error, you can use a compound trigger or store changes in a temporary table and apply them after the current operation is complete.

7. How can I prevent a trigger from firing?

You can disable a trigger using the following command:

ALTER TRIGGER trigger_name DISABLE;

To enable it again:

ALTER TRIGGER trigger_name ENABLE;

Alternatively, you can drop the trigger entirely:

DROP TRIGGER trigger_name;

8. Can I have multiple triggers on the same table?

Yes, you can have multiple triggers on the same table for different operations (INSERT, UPDATE, DELETE) or at different times (BEFORE, AFTER). However, when multiple triggers exist for the same event type, Oracle will fire them in the order they were created, unless explicitly ordered using FIRING_SEQUENCE.

9. Can triggers affect performance?

Yes, triggers can impact performance because they add extra logic that must be executed whenever the triggering event occurs. This can slow down operations, especially if the triggers contain complex logic or are executed frequently. Best Practice: Keep trigger logic as simple as possible, and avoid using them for tasks that can be handled at the application level. Also, avoid using triggers to perform large batch updates or complex calculations.

10. What is a compound trigger in Oracle?

A compound trigger allows you to define multiple timing points in a single trigger. It is especially useful when you need to avoid mutating table errors or when you need to handle multiple actions in a single trigger. For example, in a compound trigger, you can handle BEFORE INSERT and AFTER INSERT actions in the same trigger without causing mutating table issues.

11. Can I use triggers to prevent specific operations (like DELETE)?

Yes, you can use BEFORE DELETE triggers to prevent deletions by raising an exception or performing a validation check. If a condition is met (such as the record being used in other parts of the system), you can prevent the deletion. Example:

CREATE OR REPLACE TRIGGER prevent_delete

BEFORE DELETE ON employees

FOR EACH ROW

BEGIN

   IF :OLD.department_id = 'HR' THEN

      RAISE_APPLICATION_ERROR(-20001, 'Cannot delete HR department records.');

   END IF;

END;

12. Can triggers be used for logging or auditing purposes?

Yes, triggers are commonly used for auditing and logging data changes. For example, an AFTER INSERT, AFTER UPDATE, or AFTER DELETE trigger can log changes to an audit table, tracking who made the change and when. Example: An AFTER DELETE trigger for logging deletions:

CREATE OR REPLACE TRIGGER log_deletions

AFTER DELETE ON employees

FOR EACH ROW

BEGIN

   INSERT INTO audit_table (operation, emp_id, emp_name, date_deleted)

   VALUES ('DELETE', :OLD.emp_id, :OLD.emp_name, SYSDATE);

END;

13. Can I write triggers for views in Oracle?

Yes, you can use INSTEAD OF triggers to perform DML operations on views. Since views themselves do not store data, an INSTEAD OF trigger can be used to execute the DML operation on the underlying base tables. For example, an INSTEAD OF INSERT trigger on a view can direct the insert operation to the underlying table(s).

14. What happens if a trigger causes an error?

If an error occurs within a trigger (such as a PL/SQL exception), the entire operation that fired the trigger will fail, and the changes will be rolled back. You can handle exceptions within the trigger using the EXCEPTION block to control how errors are managed.

15. Can I call a stored procedure inside a trigger?

Yes, you can call a stored procedure from within a trigger. This is commonly done to modularize the logic or reuse the code. Example:

CREATE OR REPLACE TRIGGER my_trigger

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

   -- Call stored procedure

   my_procedure(:NEW.emp_id);

END;