DEV Community

Cover image for Implementing Triggers in SQL - Automating Database Actions
Luca Liu
Luca Liu

Posted on

1

Implementing Triggers in SQL - Automating Database Actions

Introdution

Triggers are special stored procedures in SQL that are automatically executed in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE. They enable automation of database actions, ensuring data consistency and integrity without requiring explicit queries. In this article, we’ll explore how triggers work, when to use them, and provide practical examples.

What are Triggers?

A trigger is a database object that is associated with a table or view and is executed automatically when a specified event occurs. Triggers are commonly used for:

  1. Data Validation: Preventing invalid data from being inserted or updated.
  2. Auditing: Logging changes made to a table.
  3. Automation: Automatically performing actions such as updating timestamps.

Types of Triggers

  1. Trigger Timing:
    BEFORE: Executes before the triggering event.
    AFTER: Executes after the triggering event.

  2. Trigger Events:
    INSERT: Triggered by an INSERT operation.
    UPDATE: Triggered by an UPDATE operation.
    DELETE: Triggered by a DELETE operation.

When to Use Triggers

Maintaining Data Consistency: Automatically enforce business rules or constraints.
Auditing Changes: Record changes to critical tables for security or debugging purposes.
Automation: Reduce the need for repetitive manual actions, such as updating timestamps or recalculating derived values.

Example 1: Automatically Updating a “Last Updated” Timestamp Column

Scenario: You want to track the last modification time of rows in the Products table.

Step 1: Create the Table

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    LastUpdated TIMESTAMP
);
Enter fullscreen mode Exit fullscreen mode

Step 2: Create the Trigger

CREATE OR REPLACE TRIGGER UpdateLastUpdated
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    :NEW.LastUpdated := CURRENT_TIMESTAMP;
END;
Enter fullscreen mode Exit fullscreen mode

How It Works:
• The trigger executes before any UPDATE operation.
• It sets the LastUpdated column of the row being updated (:NEW.LastUpdated) to the current timestamp.

Step 3: Test the Trigger

-- Insert a product
INSERT INTO Products (ProductID, ProductName, Price, LastUpdated)
VALUES (1, 'Laptop', 1200.00, CURRENT_TIMESTAMP);

-- Update the product price
UPDATE Products
SET Price = 1250.00
WHERE ProductID = 1;

-- Check the table
SELECT * FROM Products;
Enter fullscreen mode Exit fullscreen mode

Result:

ProductID ProductName Price LastUpdated
1 Laptop 1250.00 2023-12-29 10:15:30

Example 2: Enforcing Data Integrity with BEFORE INSERT Triggers

Scenario: Ensure that the Price column in the Products table is never set to a negative value.

Step 1: Create the Trigger

CREATE OR REPLACE TRIGGER ValidatePrice
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
    IF :NEW.Price < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Price cannot be negative');
    END IF;
END;
Enter fullscreen mode Exit fullscreen mode

How It Works:
• The trigger runs before an INSERT operation.
• It checks the value of :NEW.Price and raises an error if the price is negative.

Step 2: Test the Trigger

-- Attempt to insert a product with a negative price
INSERT INTO Products (ProductID, ProductName, Price, LastUpdated)
VALUES (2, 'Tablet', -500.00, CURRENT_TIMESTAMP);
Enter fullscreen mode Exit fullscreen mode

Result:

Error: ORA-20001: Price cannot be negative
Enter fullscreen mode Exit fullscreen mode

Example 3: Logging Changes to a Table with AFTER UPDATE Triggers

Scenario: Log all updates to the Products table in an audit table.

Step 1: Create the Audit Table

CREATE TABLE ProductAudit (
    AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ProductID INT,
    OldPrice DECIMAL(10, 2),
    NewPrice DECIMAL(10, 2),
    UpdatedAt TIMESTAMP
);
Enter fullscreen mode Exit fullscreen mode

Step 2: Create the Trigger

CREATE OR REPLACE TRIGGER LogProductUpdates
AFTER UPDATE ON Products
FOR EACH ROW
BEGIN
    INSERT INTO ProductAudit (ProductID, OldPrice, NewPrice, UpdatedAt)
    VALUES (:OLD.ProductID, :OLD.Price, :NEW.Price, CURRENT_TIMESTAMP);
END;
Enter fullscreen mode Exit fullscreen mode

How It Works:
• The trigger runs after an UPDATE operation.
• It logs the product ID, old price (:OLD.Price), new price (:NEW.Price), and the update timestamp into the ProductAudit table.

Step 3: Test the Trigger

-- Update a product's price
UPDATE Products
SET Price = 1300.00
WHERE ProductID = 1;

-- Check the audit table
SELECT * FROM ProductAudit;
Enter fullscreen mode Exit fullscreen mode

Result:

AuditID ProductID OldPrice NewPrice UpdatedAt
1 1 1250.00 1300.00 2023-12-29 10:30:45

Advantages of Triggers

  1. Automation: Automate repetitive tasks like logging or updating timestamps.
  2. Data Integrity: Enforce business rules directly at the database level.
  3. Audit Trail: Maintain a detailed history of changes for compliance or debugging.

Conclusion

Triggers are a powerful feature in SQL that automate database actions, enforce rules, and maintain data consistency. By using triggers like BEFORE INSERT for validation, AFTER UPDATE for logging, and other trigger types, you can ensure your database remains reliable and efficient. Use them judiciously and combine them with proper monitoring and testing to get the best results.


Explore more

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay