Triggers

• Triggers
  • Creating Trigger Functions
  • Creating Triggers
  • Enable/Disable/Dropping triggers
    • Use Cases and Best Practices
  • Performance Considerations
  • Common Pitfalls
    • The bad and the ugly of triggers
  • Conclusion on 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

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

Common Pitfalls

1. Recursive triggers

   • Triggers calling themselves through cascading updates

2. Transaction management

   • Avoid COMMIT/ROLLBACK in triggers

3. Error handling

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

4. 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:

1. Return Value:

   • Functions: Must return a value.
   • Procedures: Do not return a value directly.

2. 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.

3. 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.

4. 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:

1. Archives the old tree data
2. Records removal reason
3. Inserts new tree data
4. Updates maintenance schedule
5. 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:

1. Multiple DML Operations: The procedure performs multiple INSERT, UPDATE, and DELETE operations which is more suitable for procedures
2. Transaction Management: It needs its own transaction control (COMMIT/ROLLBACK)
3. No Return Value: The operation doesn't need to return data
4. Error Handling: Includes complex error handling with logging
5. 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.