Answer

Triggers are special database objects that automatically execute a specific piece of code (usually in SQL or procedural extension languages, such as PL/pgSQL for PostgreSQL) when an event occurs: INSERT, UPDATE, or DELETE on a table.

Advantages:

• Centralized application of business logic and data validation.
• Automation of auditing (for example, change history).

Disadvantages:

• Hidden execution — it is not always obvious that a trigger has been executed.
• Performance degradation when tables are frequently modified.
• Debugging complexities, risk of recursion (recursive triggers).
• Data migrations become more complicated.

When to apply:

• Data auditing.
• Automatic creation of related records.
• Implementation of constraint control that is not possible with standard means.

Example of a trigger (PostgreSQL):

CREATE TABLE employee_audit (
  id SERIAL PRIMARY KEY,
  employee_id INT,
  old_salary NUMERIC,
  new_salary NUMERIC,
  changed_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_salary()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO employee_audit(employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_salary();

Trick question

Is it possible to update the same table that the trigger is set on within the trigger? What will happen?

Answer:
If the trigger is configured for an event, say, an UPDATE on the table and it performs another UPDATE on the same table within itself, it will lead to infinite recursion and an emergency termination of the command or an error due to exceeding the depth of triggers. Therefore, always ensure that recursion does not occur, or use options that allow/disallow recursive calls.

Example:

-- This construction can cause the trigger to loop:
CREATE OR REPLACE FUNCTION recursive_update()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE employees SET salary = salary * 1.01 WHERE id = NEW.id; -- will trigger again
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

History

In the accounting CRM, a trigger was implemented to automatically log operations when the transactions table was changed. Under heavy load, the service became slower, and analysis showed that the trigger was taking too long to insert into the log and was not optimized (for example, it was not logging only significant events).

---

History

In the inventory management project, a BEFORE INSERT trigger was used to calculate the position ID. Incorrect ID logic caused data duplication, and the error remained unnoticed for a long time because it worked "in the background".

---

History

In the HR platform, a trigger performed mass updates to records in both parent and child tables with every INSERT. Due to errors in the trigger logic, recursion occurred, which completely blocked all operations with the tables. The trigger had to be disabled, and a complex rollback process was implemented to unlock the tables.