How to Use Event Triggers in ClickHouse

How do I create and use event triggers in ClickHouse?

Event triggers automatically run SQL or external actions when INSERT, UPDATE, DELETE, or ALTER events occur in ClickHouse tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use event triggers in ClickHouse?

Event triggers let you automate auditing, real-time ETL, and alerting without external schedulers. They execute immediately after the specified event, guaranteeing near-zero latency.

How do I create an event trigger?

Create a trigger with CREATE TRIGGER. Specify timing (AFTER), event type (INSERT, UPDATE, DELETE, ALTER), target table, and action statement or function.

What does the basic syntax look like?

The syntax resembles PostgreSQL but omits procedural languages.The action is any valid ClickHouse query.

Can I reference row data inside the trigger?

Yes. Use NEW for inserted/updated rows and OLD for deleted rows. Each is a tuple matching the table schema.

How do I disable or drop a trigger?

Disable with ALTER TRIGGER ... DISABLE.Drop permanently using DROP TRIGGER.

Best practices for production

Keep the action idempotent, avoid heavy aggregation, log errors to a dedicated table, and version-control trigger definitions.

Example: send order total to audit log

The example below writes every new order’s total to order_audit, a lightweight MergeTree table.

Full example query

CREATE TRIGGER orders_audit_trg AFTER INSERT ON Orders AS INSERT INTO order_audit (order_id,total_amount,ts) SELECT id,total_amount,now() FROM NEW;

Common mistakes

Forgetting to add a unique name

Each trigger name must be unique in the database.Append _trg or a timestamp to avoid clashes.

Putting heavy joins in the action

Slow queries inside a trigger block the original DML. Move heavy work to an asynchronous pipeline or materialized view.

FAQ

Do triggers slow down INSERTs?

Only if the action query is slow. Keep the logic lightweight or async.

Can I fire a HTTP call?

Use url table engine inside the action to insert into a remote endpoint.

Why How to Use Event Triggers in ClickHouse is important

How to Use Event Triggers in ClickHouse Example Usage


-- Capture low-stock events
CREATE TRIGGER low_stock_trg
AFTER UPDATE ON Products
AS INSERT INTO stock_alerts (product_id, remaining_stock, alert_ts)
   SELECT id, stock, now() FROM NEW WHERE stock < 10;

How to Use Event Triggers in ClickHouse Syntax


CREATE TRIGGER trigger_name
    [IF NOT EXISTS]
    AFTER {INSERT | UPDATE | DELETE | ALTER}
    ON <database>.<table>
AS <action_statement> ;

-- Ecommerce example
CREATE TRIGGER customer_created_trg
AFTER INSERT ON Customers
AS INSERT INTO signup_log (customer_id, email, created_at)
   SELECT id, email, created_at FROM NEW;

Common Mistakes

Omitting IF NOT EXISTS when deploying through CI/CD. If the trigger already exists, deployments fail. Add IF NOT EXISTS or use DROP TRIGGER before create.
Running heavy aggregations inside triggers, which blocks writes and increases latency. Refactor to a materialized view or async consumer.