Database Trigger

Q: What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written, so it can change or reject the data. An AFTER trigger runs once the change has been applied, which makes it suitable for reacting to the change, for example by writing to an audit table.

In Turkish: Veritabanı Trigger'ı

Updated September 30, 20262 min read

In short

A database trigger is code stored in the database that runs automatically when a chosen event, such as an insert, update, or delete, happens on a table.

What is a database trigger?

A database trigger is a piece of procedural code attached to a table, or sometimes to a view or the whole database, that fires automatically in response to an event. The most common events are INSERT, UPDATE, and DELETE. Applications never call a trigger directly; the database runs it whenever the event occurs, no matter which program caused it.

When you define a trigger, you choose its timing and its granularity. A BEFORE trigger runs before the change and can validate, modify, or reject the incoming row; an AFTER trigger runs once the change is made and is often used to write audit records or update summary tables; an INSTEAD OF trigger replaces the operation, typically on a view. A row-level trigger runs once per affected row and can see the old and new values, while a statement-level trigger runs once per statement. The trigger runs inside the same transaction as the statement that fired it, so if the trigger fails, the whole change is rolled back.

A trigger is like a motion-sensor light: nobody flips a switch, the event itself turns it on. Typical uses are audit trails, keeping updated_at timestamps current, enforcing rules that simple constraints cannot express, and keeping denormalized counters or search columns in sync.

A trigger is often confused with a stored procedure. A stored procedure runs only when something calls it by name, while a trigger runs implicitly when data changes, and in many databases the trigger simply calls a function. Triggers can make a system hard to understand, because logic runs that is invisible in the application code, and chains of triggers can slow down writes. Many teams keep business rules in the application and use triggers only for simple, database-level jobs such as auditing.

Key takeaways

A trigger runs automatically when data in a table is inserted, updated, or deleted.
BEFORE triggers can check or change incoming data; AFTER triggers react to finished changes.
Row-level triggers can read both the old and the new values of a row.
Triggers run inside the same transaction as the change that fired them.
Hidden trigger logic can surprise developers, so keep triggers small and documented.

Example

Keeping an updated_at column current (PostgreSQL syntax)sql

CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();  -- change the row before it is saved
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Readers ask

What is the difference between a trigger and a stored procedure?

A stored procedure runs only when an application or user calls it explicitly. A trigger runs automatically when a specific event happens on a table, and it cannot be called directly.

Are database triggers bad practice?

Not inherently, but they hide logic from people reading the application code and can slow down writes. They work well for small, database-level tasks such as auditing or timestamps; complex business logic is usually easier to test and maintain in the application.

What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written, so it can change or reject the data. An AFTER trigger runs once the change has been applied, which makes it suitable for reacting to the change, for example by writing to an audit table.