What are triggers in a relational database?

A trigger is a special type of stored procedure that automatically executes in response to a specific event in a database table or view.

Main events that invoke triggers:

• INSERT: Inserting a new row.
• UPDATE: Modifying an existing row.
• DELETE: Deleting a row.

Triggers can fire before (BEFORE) or after (AFTER) the event occurs.

Examples of trigger usage:

• Automatic field updates (e.g., last modified date).
• Data integrity checks.
• Change logging.
• Cascading updates or deletions of related data.

-- Example of creating an AFTER INSERT trigger (synthetic syntax)
CREATE TRIGGER audit_log_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_insert();

-- Example of the function called by the trigger
CREATE FUNCTION log_user_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_logs (table_name, operation, timestamp, new_data)
  VALUES ('users', 'INSERT', NOW(), row_to_json(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Advantages:

• Centralization of business logic.
• Automation of routine tasks.
• Increased data integrity.

Disadvantages:

• Debugging complexity.
• Potential impact on performance.
• Increased application logic complexity, as part of it is moved to the database level.