What is the main function of triggers in databases and in what cases are they used?

Triggers automatically perform certain actions (procedures) in response to events (INSERT, UPDATE, DELETE) that occur with data in a table.

They are used for:

• Ensuring data integrity: Maintaining consistency of data between related tables.
• Audit changes: Recording information about who, when, and how data was changed.
• Automating business logic: Executing complex rules after changes are made.
• Data replication: Synchronizing data between databases.
• Notifications: Informing other systems about changes.

Example of a trigger for logging deletions in PostgreSQL:

// Creating a table for deletion logs
CREATE TABLE deleted_products_log (
    product_id INT,
    product_name VARCHAR(255),
    deleted_at TIMESTAMP DEFAULT NOW()
);

// Creating a function that will be executed by the trigger
CREATE OR REPLACE FUNCTION log_deleted_product()
RETURNS TRIGGER AS $$
BEGIN
    // Inserting information about the deleted product into the log table
    INSERT INTO deleted_products_log (product_id, product_name)
    VALUES (OLD.id, OLD.name);
    RETURN OLD; // Returning the old row (OLD), required for AFTER DELETE triggers
END;
$$ LANGUAGE plpgsql;

// Creating a trigger that fires after deletion from the products table
CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW // Executes for each deleted row
EXECUTE FUNCTION log_deleted_product();