Database Triggers and Procedures

triggers, stored procedures, event execution, performance

Triggers

Triggers
Stored Procedures
- Example
  - Why are they called stored procedure ?

see : https://www.postgresql.org/docs/current/triggers.html

Triggers are database objects that automatically execute a function in response to certain events on a specified table or view.

Trigger functions are typically written as regular PostgreSQL function, but they have a special return type and follow specific conventions.

Creating Trigger Functions

CREATE OR REPLACE FUNCTION update_tree_audit()
RETURNS trigger AS $$
BEGIN
    -- Function logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The Return Type

Must return type trigger or event_trigger
Return values:
- NEW: Modified row for INSERT/UPDATE
- NULL: Cancel operation
- OLD: Original row for DELETE

Accessing Row Data

OLD: Previous tree data (UPDATE/DELETE)
NEW: New tree data (INSERT/UPDATE)
Example:

assume we want to track the height evolution over time in a tree_height_history table

First we create a function log_tree_height_changes that logs the new height if it’s different than the previous one.

CREATE FUNCTION log_tree_height_changes()
RETURNS trigger AS $$
BEGIN
    IF NEW.height <> OLD.height THEN
        INSERT INTO tree_height_history
        VALUES (OLD.id, OLD.height, NEW.height, current_timestamp);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Creating Triggers

Then we associate the call to the function log_tree_height_changes for each update on the

Basic Syntax

CREATE TRIGGER log_height_changes
    BEFORE UPDATE ON paris_trees
    FOR EACH ROW
    EXECUTE FUNCTION log_tree_height_changes();

Main elements of a trigger :

Timing: BEFORE, AFTER, INSTEAD OF
Event: INSERT, UPDATE, DELETE
Granularity: FOR EACH ROW, FOR EACH STATEMENT
Condition: WHEN (height IS NOT NULL)

Enable/Disable/Dropping triggers

with

ALTER TABLE paris_trees DISABLE TRIGGER log_height_changes;
ALTER TABLE paris_trees ENABLE TRIGGER log_height_changes;

Or for all triggers

ALTER TABLE paris_trees ENABLE/DISABLE TRIGGER ALL;

and dropping triggers

DROP TRIGGER IF EXISTS log_height_changes ON paris_trees;
DROP FUNCTION IF EXISTS log_tree_height_changes();

Use Cases and Best Practices

Here are a few examples that comes to mind

Audit logging

 -- Log all tree removals
 CREATE TRIGGER log_tree_removal
     AFTER DELETE ON paris_trees
     FOR EACH ROW
     EXECUTE FUNCTION record_tree_removal();

Data validation

For instance, make sure the tree height is positive! Here validate_height could flag the record as being invalid

CREATE TRIGGER validate_tree_height
   BEFORE INSERT OR UPDATE ON paris_trees
   FOR EACH ROW
   WHEN (NEW.height <= 0)
   EXECUTE FUNCTION validate_height();

The trees database sure could use more validation triggers, just sayin’!

Automated updates

 -- Update last_modified timestamp
 CREATE TRIGGER update_timestamp
     BEFORE UPDATE ON paris_trees
     FOR EACH ROW
     EXECUTE FUNCTION update_modified_column();

Performance Considerations

Keep trigger functions lightweight
Avoid triggers that call external services
Consider using AFTER triggers when possible
Use statement-level triggers for bulk operations
Index columns used in trigger conditions

Common Pitfalls

Recursive triggers
- Triggers calling themselves through cascading updates
Transaction management
- Avoid COMMIT/ROLLBACK in triggers

Error handling

BEGIN
    -- Your trigger logic
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'Error in trigger: %', SQLERRM;
        RETURN NULL;
END;

Over-reliance on triggers
- Use constraints for data validation when possible
- Consider application-level solutions for audit logging

The bad and the ugly of triggers

As with functions, putting too much business logic in triggers is literally setting future traps for the business and the tech team. Triggers on tables is far less visible than application level scripts. They are easily forgotten.

Imagine a scenario where when the startup is launched, a discount code is created for some product. They want high growth and are ready to spend money to get customers

That discount code gives a really big incentive. Like free access to all resources for 30 days. This is implemented as a trigger.

Years later, someone else in marketing comes up with the same discount code but just for a 5% price discount on some low level feature. Low and behold that trigger is still in place but no one remembers it. As users get a free for all to all the company resources … the company goes bankrupt. bye bye.

Conclusion on triggers

Use with caution and only for low level, simple operations.

Stored Procedures

A good Tutorial on procedures in SQL https://www.youtube.com/watch?v=yLR1w4tZ36I&t=425s

In PostgreSQL, both stored procedures and functions allow you to encapsulate SQL logic and reuse it.

functions are used when you need to perform calculations, transform data, or return a result set based on input parameters.
procedures are used when you need to perform a series of operations that include transaction management, complex control-of-flow logic, or when performing administrative tasks.

Here’s a breakdown of the differences:

Return Value:
- Functions: Must return a value.
- Procedures: Do not return a value directly.
Execution:
- Functions: Can be called from within SQL statements, such as SELECT, INSERT, UPDATE, or DELETE. They can be used as part of a query.
- Procedures: are executed using the CALL command and cannot be called directly within SQL statements.
Transaction Control:
- Functions: Cannot manage transactions directly. They execute within the context of a single transaction, and any errors cause a rollback of the entire transaction.
- Procedures: Can manage transactions directly using commands like BEGIN, COMMIT, and ROLLBACK. This allows more fine-grained control over transactions, making procedures more suitable for complex business logic.
Side Effects: (as in modify the data )
- Functions: Generally, functions are expected to be deterministic (i.e., they should not have side effects and should produce the same result given the same input). However, in PostgreSQL, you can write functions with side effects, but this is not the typical use case.
- Procedures: Are designed to perform actions that have side effects, such as modifying tables or performing administrative tasks.

Example

Here is an example of a stored procedure that handles a complex business operation related to tree management where a procedure would be more appropriate than a function.

This procedure handles the complete process of tree replacement:

Archives the old tree data
Records removal reason
Inserts new tree data
Updates maintenance schedule
Sends notification (simulated with a log entry)

CREATE OR REPLACE PROCEDURE replace_tree(
    p_old_tree_id INTEGER,
    p_removal_reason TEXT,
    p_new_species VARCHAR(100),
    p_new_height NUMERIC,
    p_new_diameter NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_tree_id INTEGER;
BEGIN
    -- Start transaction
    -- Procedures can manage their own transactions, unlike functions
    BEGIN
        -- 1. Archive old tree data
        INSERT INTO tree_archives (
            old_tree_id,
            removal_date,
            removal_reason,
            species,
            height,
            diameter
        )
        SELECT
            id,
            CURRENT_DATE,
            p_removal_reason,
            species,
            height,
            diameter
        FROM paris_trees
        WHERE id = p_old_tree_id;

        -- 2. Delete old tree
        DELETE FROM paris_trees WHERE id = p_old_tree_id;

        -- 3. Insert new tree
        INSERT INTO paris_trees (
            species,
            height,
            diameter,
            planting_date
        ) VALUES (
            p_new_species,
            p_new_height,
            p_new_diameter,
            CURRENT_DATE
        ) RETURNING id INTO v_new_tree_id;

        -- 4. Create maintenance schedule for new tree
        INSERT INTO maintenance_schedule (
            tree_id,
            next_inspection_date,
            maintenance_type
        ) VALUES (
            v_new_tree_id,
            CURRENT_DATE + INTERVAL '6 months',
            'Initial Growth Check'
        );

        -- 5. Log the operation
        INSERT INTO operation_logs (
            operation_type,
            details
        ) VALUES (
            'TREE_REPLACEMENT',
            format('Old tree %s replaced with new tree %s. Reason: %s',
                   p_old_tree_id, v_new_tree_id, p_removal_reason)
        );

        -- Commit transaction
        COMMIT;

    EXCEPTION WHEN OTHERS THEN
        -- Roll back all changes if any step fails
        ROLLBACK;
        -- Log the error
        INSERT INTO operation_logs (operation_type, details)
        VALUES ('ERROR', 'Tree replacement failed: ' || SQLERRM);
        -- Re-raise the error
        RAISE;
    END;
END;
$$;

-- Example usage:
CALL replace_tree(
    p_old_tree_id := 1234,
    p_removal_reason := 'Disease - Dutch Elm',
    p_new_species := 'Platanus x hispanica',
    p_new_height := 3.5,
    p_new_diameter := 0.1
);

This is a good example of when to use a procedure instead of a function because:

Multiple DML Operations: The procedure performs multiple INSERT, UPDATE, and DELETE operations which is more suitable for procedures
Transaction Management: It needs its own transaction control (COMMIT/ROLLBACK)
No Return Value: The operation doesn’t need to return data
Error Handling: Includes complex error handling with logging
Side Effects: Deliberately creates side effects (logging, multiple table updates)

Functions, in contrast, would be more appropriate for:

Calculating tree growth rates
Validating tree measurements
Computing distances between trees
Returning sets of trees based on criteria

Why are they called stored procedure ?

The term “stored” procedure comes from how they evolved in database systems:

In early database systems, procedures were executed client-side. When databases began supporting server-side procedures, they were called “stored” to differentiate them. The code is literally “stored” in the database catalog, unlike client procedures

Modern databases like PostgreSQL simply use “procedure” in their syntax (CREATE PROCEDURE), but the term “stored procedure” persists in database terminology due to historical reasons and to emphasize their server-side nature.