Oracle - 19 - Triggers

Start MCQ

In Oracle, triggers are database objects that are automatically executed in response to specific events or conditions that occur in the database. Triggers are used to enforce data integrity, implement business rules, and automate tasks when certain database operations occur. Here’s a brief description of Oracle triggers:

Key Concepts:

Event or Condition:
- Triggers are associated with specific database events or conditions, such as data changes (INSERT, UPDATE, DELETE), schema-level events (CREATE, ALTER), or system events (LOGON, LOGOFF). Triggers are defined to respond to these events.
Trigger Timing:
- Triggers can be classified into two main categories based on their timing:
  - Before Triggers: These triggers fire before the triggering event, allowing you to validate or modify data before it is actually changed.
  - After Triggers: These triggers fire after the triggering event has occurred, typically used for auditing or logging changes.
Row-Level and Statement-Level Triggers:
- Triggers can operate at either the row level or the statement level.
  - Row-Level Triggers: These triggers execute once for each affected row in a DML operation (e.g., for each row in an INSERT, UPDATE, or DELETE statement).
  - Statement-Level Triggers: These triggers execute once for each triggering SQL statement, regardless of the number of rows affected.

Syntax:

The basic syntax for creating a trigger in Oracle is as follows:

CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
DECLARE
— Declaration of local variables
BEGIN
— Trigger logic
END trigger_name;

Usage:

Enforcing Data Integrity:
- Triggers can be used to enforce complex data integrity rules and constraints that cannot be implemented using standard constraints (e.g., checking data across multiple tables).
Auditing and Logging:
- After triggers are commonly used to log changes to the database for auditing purposes. They can capture the old and new values of modified data.
Data Transformation:
- Triggers can modify data before it is inserted, updated, or deleted. For example, they can automatically generate and insert audit information.
Complex Business Rules:
- Triggers are helpful for implementing complex business rules that involve multiple data changes or calculations based on certain conditions.

Example:

-- Example of an audit trail trigger
CREATE OR REPLACE TRIGGER audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
   action VARCHAR2(20);
BEGIN
   IF INSERTING THEN
      action := 'INSERT';
   ELSIF UPDATING THEN
      action := 'UPDATE';
   ELSIF DELETING THEN
      action := 'DELETE';
   END IF;

   INSERT INTO audit_log (action_type, employee_id, timestamp)
   VALUES (action, :OLD.employee_id, SYSTIMESTAMP);
END audit_employee_changes;

In this example, the audit_employee_changes trigger is set to execute after any INSERT, UPDATE, or DELETE operation on the employees table. It records the action type (INSERT, UPDATE, DELETE), the affected employee’s ID, and a timestamp in an audit_log table.

Triggers in Oracle are a powerful tool for automating database actions, enforcing data integrity, and implementing business logic. However, they should be used judiciously, as poorly designed triggers can introduce complexity and performance overhead.

Oracle – 19 – Triggers