Triggers in PL/SQL

Triggers in PL/SQL

Introduction to Triggers

A trigger is a predefined set of SQL statements that automatically execute when a specific event occurs in a database. These events are typically Data Manipulation Language (DML) statements like INSERT, UPDATE, and DELETE, but triggers can also respond to Data Definition Language (DDL) statements or system events.

Triggers play a crucial role in enforcing business rules, maintaining data integrity, and automating repetitive tasks. Unlike stored procedures, which require explicit execution, triggers fire automatically when their associated event occurs.

Uses of Triggers

Triggers are essential in database management systems for various reasons, including:

1. Enforcing Business Rules – Ensure compliance with company policies by validating data before insertion or modification.
2. Auditing Changes – Log database modifications to track user actions and prevent unauthorized changes.
3. Maintaining Referential Integrity – Automatically enforce foreign key constraints and maintain consistency across related tables.
4. Automating Tasks – Perform automatic computations or updates when changes occur in the database.
5. Preventing Invalid Transactions – Restrict unauthorized modifications or operations that violate predefined conditions.

Types of Triggers

Triggers in PL/SQL can be categorized based on their execution timing and the type of database event they respond to.

1. Based on Execution Timing:

Triggers can be classified into three main categories based on when they execute in relation to the DML operation:

• BEFORE Trigger – Executes before an INSERT, UPDATE, or DELETE operation takes place.
• AFTER Trigger – Executes after an INSERT, UPDATE, or DELETE operation is completed.
• INSTEAD OF Trigger – Specifically used on views, allowing us to define actions when a DML statement is applied to a view.

2. Based on the Type of Event:

Triggers are also categorized based on the type of event that activates them:

• DML Triggers – Respond to INSERT, UPDATE, or DELETE operations.
• DDL Triggers – Fire when CREATE, ALTER, DROP, or other schema-related statements are executed.
• Database Triggers – Execute based on system events like user logins, session creation, or startup/shutdown of the database.

1. DML Triggers

DML (Data Manipulation Language) triggers execute in response to changes in data. These include:

• INSERT Trigger – Fires when a new row is inserted into a table.
• UPDATE Trigger – Fires when existing records are updated.
• DELETE Trigger – Fires when records are deleted from a table.

Example Use Cases of DML Triggers:

• Audit Trails – Automatically log user modifications to a table.
• Data Validation – Ensure that data meets specific conditions before insertion.
• Cascade Updates or Deletes – Automatically adjust related records when changes occur.

2. DDL Triggers

DDL (Data Definition Language) triggers are used to monitor and control database structure changes such as:

• CREATE Trigger – Fires when a table, view, or other object is created.
• ALTER Trigger – Fires when an object is modified.
• DROP Trigger – Fires when an object is deleted.

Example Use Cases of DDL Triggers:

• Preventing Unauthorized Schema Changes – Restrict users from altering or dropping tables.
• Auditing DDL Operations – Log schema modifications for security purposes.
• Automated Notifications – Notify administrators about schema changes.

3. Database Triggers

Database triggers fire on system-wide events and can be used for:

• User Login Tracking – Log user login attempts for security monitoring.
• Session Management – Restrict certain users from logging in at specific times.
• Backup Operations – Automate backups when a session starts or ends.

Example Use Cases of Database Triggers:

• Security Enforcement – Prevent unauthorized logins.
• Performance Optimization – Clear temporary tables when a session ends.
• Automated Data Backups – Initiate backups upon shutdown.

RAISE_APPLICATION_ERROR Procedure

PL/SQL provides the RAISE_APPLICATION_ERROR procedure to explicitly raise a user-defined exception and return custom error messages. This is helpful in triggers for enforcing business rules or restricting invalid operations.

Why Use RAISE_APPLICATION_ERROR?

1. Prevent Invalid Transactions – Stop execution when data does not meet business rules.
2. Improve Debugging – Provide meaningful error messages for better troubleshooting.
3. Enhance Security – Restrict unauthorized access or modifications.
4. Custom Error Codes – Allow returning specific error numbers (-20000 to -20999) with descriptive messages.

Syntax:

RAISE_APPLICATION_ERROR(error_number, error_message);

Use Cases in Triggers:

• Prevent deletion of important records.
• Restrict updates to specific conditions.
• Enforce additional validation rules.

Best Practices for Using Triggers

To ensure that triggers are effective and do not negatively impact database performance, consider the following best practices:

1. Avoid Excessive Use of Triggers – Overuse of triggers can lead to performance issues and complex debugging.
2. Use Triggers for Business Rules Enforcement – Instead of application logic, use triggers to ensure data integrity.
3. Keep Trigger Logic Simple – Complex triggers can make debugging and maintenance difficult.
4. Use AFTER Triggers for Logging – Logging changes is best handled using AFTER triggers.
5. Use BEFORE Triggers for Validation – BEFORE triggers are useful for preventing invalid data entry.