Triggers

In Oracle Database, there are two main types of triggers:

• DML Triggers (Data Manipulation Language) and
• DDL Triggers (Data Definition Language).

Each type of trigger can be further classified based on the timing of their execution and the level at which they apply.

DML Triggers

DML triggers are associated with Data Manipulation Language statements, such as INSERT, UPDATE, DELETE, and MERGE. They execute in response to changes in data within a table.

Row-Level Triggers

Row-level triggers fire for each affected row, i.e., for each row that is inserted, updated, or deleted.

Example of a Row-Level Trigger:

CREATE OR REPLACE TRIGGER after_update_employee
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  -- Log the update in the audit table or history table
  INSERT INTO employee_audit (employee_id, action, action_date)
  VALUES (:OLD.employee_id, 'UPDATE', SYSDATE);
END;

In this example, the trigger “after_update_employee” is defined as an AFTER UPDATE trigger on the “employees” table. It fires for each row updated in the table and logs the update action into the “ employee_audit” table.

Statement-Level Triggers

Statement-level triggers fire once for each triggering event, regardless of the number of rows affected by the DML statement.

Example of a Statement-Level Trigger:

CREATE OR REPLACE TRIGGER after_insert_employee
AFTER INSERT ON employees
FOR EACH STATEMENT
BEGIN
  -- Log the insert action in the audit table
  INSERT INTO employee_audit (employee_id, action, action_date)
  SELECT employee_id, 'INSERT', SYSDATE FROM employees;
END;

In this example, the trigger “after_insert_employee” is defined as an AFTER INSERT trigger on the “employees” table. It fires once for the entire insert statement and logs the insert action into the “employee_audit” table.

DDL Triggers

DDL triggers are associated with Data Definition Language statements, such as CREATE, ALTER, and DROP. They execute in response to changes in database objects or the schema.

Example of a DDL Trigger:

CREATE OR REPLACE TRIGGER ddl_audit_trigger
AFTER DDL ON DATABASE
BEGIN
  -- Log the DDL statement and its timestamp in the audit table
  INSERT INTO ddl_audit (ddl_statement, ddl_timestamp)
  VALUES (ora_sql_txt, SYSDATE);
END;

In this example, the trigger “ddl_audit_trigger” is defined as an AFTER DDL trigger on the entire database. It logs any DDL statement executed in the database, along with its timestamp, into the “ddl_audit” table.

It’s important to note that DDL triggers are powerful and can impact the entire database. They should be used with caution and only for essential auditing or administrative purposes.