SQL EACH

What is the SQL EACH keyword?

Indicates that a trigger should fire once for every affected row (row-level trigger) when used in the FOR EACH ROW clause of CREATE TRIGGER statements.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EACH: PostgreSQL, MySQL, MariaDB, Oracle, SQLite (3.35+), IBM Db2, Snowflake, Redshift. BigQuery supports EACH only in legacy SQL for JOIN EACH and is deprecated in Standard SQL.

SQL EACH Full Explanation

EACH is not a stand-alone command but a reserved word that appears inside the FOR EACH ROW clause of CREATE TRIGGER. When a trigger is declared FOR EACH ROW, the database executes the trigger body separately for every row inserted, updated, or deleted, rather than once per statement. This enables row-by-row auditing, validation, denormalized updates, and other fine-grained logic. If EACH is omitted (or FOR EACH STATEMENT is specified), the trigger fires only once per DML statement, regardless of the number of affected rows. Some dialects, notably Google BigQuery legacy SQL, also used EACH in JOIN EACH or SELECT EACH to control sharding behavior, but that usage is deprecated. The modern, widely supported context for EACH is strictly within trigger definitions.

SQL EACH Syntax

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
FOR EACH ROW
[ WHEN (condition) ]
EXECUTE FUNCTION function_name();

SQL EACH Parameters

Example Queries Using SQL EACH

-- Audit table that records every row inserted into users
CREATE TABLE user_audit(
  user_id     INT,
  full_name   TEXT,
  changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_user_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO user_audit(user_id, full_name)
  VALUES (NEW.id, NEW.name);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_user_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_insert();

Expected Output Using SQL EACH

After the trigger is created, every row inserted into users causes a corresponding row to be written to user_audit
The DML statement that inserts multiple rows will invoke the trigger once per row

Use Cases with SQL EACH

Capture row-level audit trails
Enforce complex validation on individual rows
Maintain denormalized summary tables
Cascade changes to child tables when a parent row changes

Common Mistakes with SQL EACH

Omitting EACH and unintentionally creating a statement-level trigger
Expecting NEW and OLD row variables inside a FOR EACH STATEMENT trigger (they are only available with FOR EACH ROW)
Performing long-running logic in row-level triggers, which can degrade performance
Forgetting to RETURN NEW/OLD in AFTER row triggers in PostgreSQL, causing null results

First Introduced In

SQL:1999 (trigger feature)

Frequently Asked Questions

What is the difference between FOR EACH ROW and FOR EACH STATEMENT?

FOR EACH ROW runs the trigger once per row, giving access to NEW and OLD values. FOR EACH STATEMENT fires only once after the statement finishes and cannot access row data.

Can I change a row inside a FOR EACH ROW trigger?

Yes. BEFORE row triggers can modify NEW values before they are written. AFTER row triggers can only read the data because the change has already occurred.

Does using EACH harm performance?

Row-level triggers add overhead proportional to the number of affected rows. For bulk operations consider statement-level triggers or ETL processes to keep performance acceptable.

Is EACH still needed in BigQuery joins?

No. Google BigQuery Standard SQL removed JOIN EACH. Modern queries should omit EACH and rely on the optimizer to handle large joins.