PL/SQL Statement level Triggers

Statement-level triggers in Oracle databases execute actions for each transaction, responding to various database events like DML and DDL statements, system events, and user interactions. They act as programmed responses to specific table events, enhancing database management and automation.

Stored as named PL/SQL blocks, triggers execute automatically upon occurrence of a predefined event, crucial for efficient database operation.

In this article, we will cover PL/SQL Statement level Triggers syntax, their importance, characteristics, common use cases, and examples, illustrating their role in Oracle database management.

PL/SQL Statement Level Triggers

Statement-level triggers are said to be specialized PL/SQL code blocks that execute once per triggering DML statement like, Insert, Update, and Delete on any table. How about a glimpse of its structure?

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name //start
    {BEFORE | AFTER } triggering_event 
    ON table_name
    [FOLLOWS | PRECEDES another_trigger]
    [ENABLE / DISABLE ]
    [WHEN condition]
DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;

Importance of Statement-Level Triggers

*NOTE: Assume that you have created a database for some customers with constraints like, orders, sales etc.

• Enforcement of Complex Data Rules: They can enforce complex data rules beyond what constraints can handle like deleting a customer’s entry in database and automatically removing their related orders.
• Automation of Tasks: They tend to automate tasks based on database events (Insert, Update, Delete). Perhaps it can update a “total sales” table whenever a new order is placed, saving oneself, the time and effort to write a separate code to maintain accurate sales figures.
• Centralization of Actions: They can centralize actions into reusable code blocks, making your code neat, easy to read & understand, and manage. One has to create multiple procedures that need to perform common actions like logging changes after inserting data into different tables. Instead of duplicating that code, one create a trigger that fires after any insert operation and performs the centralized logging action.
• Enhanced Security: They can perform extra security checks and/or validate data to prevent unauthorized access or vulnerabilities. If a trigger could be set to fire before deleting a user account, then it could check if the user, attempting the deletion, has the necessary privileges to perform such an action.

Characteristics of Statement-Level Triggers

• It can only be executed once per triggering DML statement.
• For each row, clause is not present here.
• It usually differs from row-level triggers that iterate per row.
• Access to : old and: new pseudo records might be limited based on timing (Before or After).

Common Use Cases

• It reduces code duplicacy and improves code maintainability.
• It can log information about database modifications like user, timestamp, and table affected.
• It tends to validate data and enforce rules beyond database constraints.
• It can even add extra security checks before or after DML statements.

Example of PL/SQL Statement Level Triggers

Let us assume the same example. Suppose there exists a table named customers.

Example 1: Making An Audit Customer Update Trigger

Here, I made a trigger that fires or executes whenever there’s an update operation performed on the customers table. It tends to track changes made to customer information and record these changes in an audit table, named customer_audit.

CREATE OR REPLACE TRIGGER cust_update_audit
AFTER UPDATE ON customers
FOR EACH ROW
WHEN (NEW.name != OLD.name OR NEW.email != OLD.email OR NEW.phone != OLD.phone)
BEGIN
  INSERT INTO customer_audit (customer_id, old_name, old_email, old_phone, new_name, new_email, new_phone, update_time)
  VALUES (:OLD.customer_id, :OLD.name, :OLD.email, :OLD.phone, :NEW.name, :NEW.email, :NEW.phone, SYSDATE);
END;
/

Let us assume we have the following entry in the customers table.

Entry in the table

Query:

    UPDATE customers
     SET name = 'GFG',
         email = 'GFG@EMAIL.COM'
     WHERE customer_id = 1;

Output:

Sample output

Explanation: The customer_audit table will now be updated with a new entry. But only the name will be changed, nothing else as per the above query or we can say, that, the trigger captures the old and new values of the updated customer and logs them into the customer_audit table.

Example 2: Making An Automatic Discount Trigger

Again I made a trigger that automatically applies a discount to a customer’s order if the total order amount exceeds a specified threshold. I set the threshold as $100 for now. Assume that we are using data from another table named Orders.

CREATE OR REPLACE TRIGGER order_discount
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF :NEW.order_total > 100 THEN
    :NEW.order_total := :NEW.order_total * 0.9; -- Applying 10% discount
  END IF;
END;
/

INSERT INTO orders (order_id, customer_id, order_total)
VALUES (1, 1, 120);

Output:

updated table

Explanation: Before the insertion query is executed, the trigger will check the order_total of the new order. But since the order_total (as per query, it is $120) exceeds $100, the trigger will apply a 10% discount. After trigger execution, the order_total for this order will be updated to $108. The orders table gets updated accordingly.

Conclusion

In Conclusion, PL/SQL Statement level Triggers are essential tools in Oracle database management, automating actions in response to specified events. Their syntax, importance, characteristics, and common use cases demonstrate their critical role in maintaining data integrity and enhancing security. Understanding and utilizing these triggers streamline database operations, ensuring efficiency and reliability in handling transactions and events.