Have you worked with triggers? What did you use them for? What types of triggers do you know (ROW vs STATEMENT)?

Triggers in databases are used to automatically perform specific actions when events (INSERT, UPDATE, DELETE) occur in tables. I have used triggers for auditing data changes, automatically updating related tables, and maintaining integrity.

There are two main types of triggers based on the level of activation:

• ROW-level — the trigger fires for each row that is modified. For example, when 10 rows are updated, the trigger will execute 10 times.
• STATEMENT-level — the trigger fires once for the entire SQL statement, regardless of the number of affected rows.

An example of creating a row-level trigger in PostgreSQL:

CREATE FUNCTION audit_changes() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION audit_changes();