PL/SQL DML triggers

PL/SQL DML triggers in Oracle database are used to automatically execute a set of actions or procedures when a data manipulation language (DML) operation is performed on a table. These triggers are used to enforce business rules and data integrity constraints, audit changes to data, and perform custom actions when data is changed.

DML triggers can be defined on tables or views and can be fired on events like INSERT, UPDATE, or DELETE. When a DML operation is performed on the table, the corresponding trigger is executed before or after the operation, depending on how the trigger is defined.

Syntax

PL/SQL DML triggers can be created using the CREATE TRIGGER statement in Oracle. The basic syntax of creating a DML trigger is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name
[FOR EACH ROW]
DECLARE
   -- variable declarations
BEGIN
   -- trigger logic
END;

The “BEFORE” or “AFTER” keyword specifies when the trigger is fired, while the “INSERT”, “UPDATE”, or “DELETE” keyword specifies the DML operation that will fire the trigger. The “FOR EACH ROW” clause specifies that the trigger will be fired for each row affected by the DML operation.

The trigger logic is defined in the DECLARE and BEGIN-END block. The trigger can access the old and new values of the affected rows using the “OLD” and “NEW” qualifiers, respectively. For example, the “OLD” qualifier refers to the row before the update or delete operation, while the “NEW” qualifier refers to the row after the update or insert operation.

In addition to accessing the old and new values, PL/SQL DML triggers can also raise exceptions and perform actions like inserting or updating data in other tables.

Example

Here’s an example of a PL/SQL DML trigger that fires after an INSERT operation on a table named “employees”.

CREATE OR REPLACE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
   -- Update the department table to increment the count of employees in the new employee's department
   UPDATE department
   SET employee_count = employee_count + 1
   WHERE department_id = :NEW.department_id;
END;
/

In this trigger, the AFTER INSERT clause specifies that the trigger should be fired after an INSERT operation on the “employees” table. The FOR EACH ROW clause indicates that the trigger should be executed for each row affected by the INSERT operation.

Inside the trigger, we use the :NEW qualifier to reference the values of the newly inserted row. In this example, we’re updating the “department” table to increment the count of employees in the department of the newly inserted employee.

Overall, PL/SQL DML triggers in Oracle database are a powerful tool for enforcing data integrity constraints, auditing changes to data, and performing custom actions when data is changed. By defining triggers, developers can automate certain tasks, improve the accuracy of data in the database, and ensure that the database remains consistent and reliable.